FastLoad fails

[bsummey]

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!

[David_Roth]

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

[bsummey]

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)
);

[Fred · Accepted solution]

FastLoad requires two digit month, day, hour and your data does not have leading zeros.