SQL to import string with comma (,) from a file using SQL Assistant

Database

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!

Tags (4)
3 REPLIES

Re: SQL to import string with comma (,) from a file using SQL Assistant

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!

N/A

Re: SQL to import string with comma (,) from a file using SQL Assistant

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

Dieter

Re: SQL to import string with comma (,) from a file using SQL Assistant

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