Hello - I have an external file that I need to load to a table and it fails everytime. When I go through the data load wizard, it shows a sample of my data and everything looks correct, but when I press Finish, the load fails. I have tried loading to an existing and volatile table, both fail. Below is sample data, data load wizard select and existing table ddl.
Any ideas?
SAMPLE DATA:
"ClientID"|"SendID"|"SubscriberKey"|"EmailAddress"|"SubscriberID"|"ListID"|"EventDate"|"EventType"|"BatchID"|"TriggeredSendExternalKey"
7280191|123465|"test@test.com"|"test@test.com"|25603738|839|1/2/2018 10:56:25 AM|"Unsubscribe"|84|""
7280191|126293|"test@test.com"|"test@test.com"|25564752|839|1/3/2018 12:54:57 PM|"Unsubscribe"|2|""
7280191|57740|"23604012_1_19155"|"test@test.com"|101820336|671|1/2/2018 10:53:20 AM|"Unsubscribe"|5622|"4893"
7280191|57739|"26847852_1_19806"|"test@test.com"|90008770|671|1/2/2018 10:56:15 AM|"Unsubscribe"|5924|"4894"
DATA LOAD WIZARD:
checked columns in first row
column delimiter = |
character string delimiter = "
line separator = Windows OS (CR and LF)
file encoding = Cp1252 (also tried US-ASCII)
DDL
CREATE SET TABLE import_hold2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
client_id INTEGER,
send_id INTEGER,
subscriber_key VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
email_address VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
subscriber_id INTEGER,
list_id INTEGER,
event_date VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC,
event_type VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
batch_id INTEGER,
triggered_send_external_key VARCHAR(4) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( client_id );
Thanks!
Solved! Go to Solution.
What error message do you get?
The timestamp should fail because it is defined as a varchar in the table but in the data file it is not surrounded by quotes, and you have said it should be.
Dave
Thanks for catching that! I changed the format to TIMESTAMP(0), but it still won't load. Do I have the correct format?
CREATE TABLE data_lab_phss_tbls.import_hold
(
client_id INTEGER,
send_id INTEGER,
subscriber_key VARCHAR(100),
email_address VARCHAR(100),
subscriber_id INTEGER,
list_id INTEGER,
event_date TIMESTAMP(0),
event_type VARCHAR(10),
batch_id INTEGER, triggered_send_external_key VARCHAR(4)
);