ODBC Output with delimiters in values

[CRP0021]

Hi all,
Just looking for a bit of advice to see if there is an easy way to solve this before I go down a path that will cause a lot of time/effort to be spent on developing something.

Here are some details of what I'm trying to do:

I have a cube that I'd like to export to a SQL DB.
The cube has 80+ measures that need to be exported with a mix of numeric and string values.

The string values are causing issues while doing a simple insert to the SQL table as they contain possible delimiter values (i.e. single quote, double quote, commas)

As an example of a an string value;
Jé 12 - DC Sacacoyo " Km 00.1 123 Anywhere, Any'where Avenue

A sample of the TI code is as follows:

## Insert into SQL table
sSQL = 'INSERT INTO [MYDB].[MYTable] ( [RecordID], [Address], [Account], [Time] )

VALUES ( ''' | vID | ''', ''' | sQualifiedString1 | ''', ''' | vAccount | ''',''' | sTime | ''' )';

OdbcOutput ( cODBC, sSQL );

I can parse out the single and double quote but am unable to remove the comma and what's happening is that the text after the comma is being inserted into the next field. So my sQualifiedString1 is being cutoff at the comma, and the remaining string after the text is being inserted to the account field.

I'm hoping there is a cleaner way than checking every string cell for possible delimiter characters and any advice would be greatly appreciated.

Thank you.

[declanr]

I can parse out the single and double quote but am unable to remove the comma and what's happening is that the text after the comma is being inserted into the next field. So my sQualifiedString1 is being cutoff at the comma, and the remaining string after the text is being inserted to the account field.

How are you parsing out the quotes?
As long as it gets parsed correctly you shouldn't have any issues with commas, as long as the commas are inside of quotes - it will just be considered part of the string.
You also shouldn't need to do anything to parse out a double quote, again because it is inside of your quotes, the only thing you should need to parse is the apostrophes.

So in your example:
Jé 12 - DC Sacacoyo " Km 00.1 123 Anywhere, Any'where Avenue

You just need to convert it by replacing any apostrophe with 2 apostrophes e.g.:
Jé 12 - DC Sacacoyo " Km 00.1 123 Anywhere, Any''where Avenue

Your question intrigued me as I have done a lot of ODBC outputs and not had any issues with commas in this way so I did test it using your specific string example and it worked ok for me.
I just loop over the string searching for any instance of the offending character (apostrophe in this case) and replace it with 2 instead:

Code: Select all

# Character to replace 39 is the ascii character for an apostrophe
sCharacter = Char ( 39 );

# Clear sFinalString variable
sFinalString = '';
# Set sSubstring to entire sAddress variable
sSubstring = sAddress;

# Find first instance of apostrophe, if there is none skip while loop
iFind = Scan ( sCharacter, sSubstring );
# Keep looping when any apostrophe is found (in case of multiple)
While ( iFind > 0 );
	# Concatenate sFinalString to include sSubstring up to the next apostrophe and add an additional apostrophe
	sFinalString = sFinalString | SubSt ( sSubstring, 1, iFind  ) | sCharacter;
	# For sSubstring leave as the remainder that is not included in sFinalString
	sSubstring = SubSt ( sSubstring, iFind + 1, Long ( sSubstring ) - iFind );
	# Find the next apostrophe
	iFind = Scan ( sCharacter, sSubstring );
End;
# Add any remaining text to the end of sFinalString
sFinalString  = sFinalString | sSubstring;

# Construct Insert Statement
sSQL = Expand ( 'INSERT INTO [MYDB].[MYTable] ( [RecordID], [Address], [Account], [Time] ) VALUES ( ''%vID%'', ''%sFinalString%'', ''%vAccount%'', ''%sTime%'' );' );

# Execute insert statement
ODBCOutput ( cODBC, sSQL );

The above is true for SQL Server and most DBs work the same way. Of course, there are some finickity differences with certain DBs.

I should add that on occasions where I have constructed SQL statements that don't work - I change it to a TextOutput and then copy the text into something like SSMS/Toad etc. where it is normally easier to deconstruct and see where the issue is.