All Forums Database
thisisKumar 13 posts Joined 09/11
30 Apr 2013
SQL to import string with comma (,) from a file using SQL Assistant

Hi, I am trying to import data from a text file using a SQLAssitant. But it is giving me an error as the string contains ",".
I am using this below syntax for inserting:
 

CREATE VOLATILE TABLE kumar_test (a INTEGER, b VARCHAR(100))

PRIMARY INDEX  ( a );

 

INSERT INTO kumar_test(?,?)

 

my file contains:

1,a

2,b

3,c

4,d

5,cd

6,'e,f'

7, x's y

 

I am able to import first 5 records sucessfully. But, I am getting an error at record 6. here I have added a another record (7) to make sure I won't get an error in the future for this scenario.

 

Thanks!

thisisKumar 13 posts Joined 09/11
02 May 2013

ok. I got the temporary solution. Because we dont get pipe in the actual data. So, I saved the file with '|' as delimiter. then I changed the delimiter from comma to a pipe in TDA.
Thanks!

dnoeth 4628 posts Joined 11/04
02 May 2013

I didn't test it, but did you try to set "enclose column data in" in options -> import/export to "single quotes"?
Dieter

Dieter

thisisKumar 13 posts Joined 09/11
03 May 2013

No, Dieter. I did not try before.
I tested it now. It is working by selection option: Enclose column data in "Single Quotes". Thank you!
I tested with above sample code. It's working. But I am not sure if i can use this same techincque for actual file because I have 50 columns in actual file. I need to put single quote to all 20 to 30 records and 50 columns.
-Kumar

You must sign in to leave a comment.