I'm trying to create a Format File for a bulk insert into SQL because I have a comma delimited file with some currency fields that have a comma to separate the thousands and it's causing issues on import. The currency fields have a text qualifier of a ".
I've attempted to write an import file but I'm still getting some data type mismatch errors when importing. More specifically on the date field and the paid amount fields. I can get the date field to import if I make it a SQLCHAR and a varchar(20) in my SQL table, but I'm hoping to import it as a date. If I open my .csv files in Excel the date fields are all Data format and D/M/YYYY.
My Currency fields are stored as currency in Excel with - numbers in (). My table in SQL is created using the money data type for those fields.
There's also a units field that is stored as a number with a comma for a thousands separator so I'm assuming that has the same " text qualifier. (I think I missed that in my format file, but I'll update it to whatever I use for the currency fields as a delimiter).
I've uploaded an example of the data and my attempt at my format file. Any help is appreciated. Thanks.
I've attempted to write an import file but I'm still getting some data type mismatch errors when importing. More specifically on the date field and the paid amount fields. I can get the date field to import if I make it a SQLCHAR and a varchar(20) in my SQL table, but I'm hoping to import it as a date. If I open my .csv files in Excel the date fields are all Data format and D/M/YYYY.
My Currency fields are stored as currency in Excel with - numbers in (). My table in SQL is created using the money data type for those fields.
There's also a units field that is stored as a number with a comma for a thousands separator so I'm assuming that has the same " text qualifier. (I think I missed that in my format file, but I'll update it to whatever I use for the currency fields as a delimiter).
I've uploaded an example of the data and my attempt at my format file. Any help is appreciated. Thanks.