Import/Insert .txt file with double quotes delimiter issue

Database
Enthusiast

Import/Insert .txt file with double quotes delimiter issue

Hello - I am having an issue importing a text file that is pipe-delimited with double quotes delimiter around character data, but no quotes around numeric data. How do I change the INSERT code and IMPORT settings to allow for those variances?

 

Here is how the text file data looks:

"ClientID"|"SendID"|"SubscriberKey"|"EmailAddress"|"SubscriberID"

7280191|57736|"26762183_1_19891"|"test1@email.com"|82968437
7280191|57736|"21402375_1_19891"|"test2@email.com"|90009927

 

When I have the settings like this, I get the error 'Wrong number of data values in record 1'

Export/Import Settings:Use this delimter between columns = | AND Enclose column data in = double quotes

Import Settings: Ignore the first record = checked

 

When I have the setting likes this, the double quotes are being imported into the character fields (table shows email address as "test1@email.com", want it to just be test1@email.com without quotes)

Export/Import Settings: Use this delimter between columns = | AND Enclose column data in = nothing

Import Settings: Ignore the first record = checked

 

 

CREATE TABLE test_table (client_id INTEGER, send_id INTEGER, subscriber_key VARCHAR(100), email_address VARCHAR(100), subscriber_id INTEGER);

INSERT INTO  test_table VALUES (?,?,?,?,?);

 

 

2 REPLIES
Teradata Employee

Re: Import/Insert .txt file with double quotes delimiter issue

Teradata Parallel Transporter's Data Connector operator supports "Optional" quotes in delimited input. So does Teradata Studio's "Smart Load" feature. SQL Assistant does not.

Enthusiast

Re: Import/Insert .txt file with double quotes delimiter issue

Sorry, using SQLA.