Hi There,
This is a strange one (for me). I'm trying to import data from the GDELT project (http://gdeltproject.org/), one of the files is attached.
I have MS SQL installed on my PC. When I try to do bulk import from these files, I get errors about data truncating. On examining the files, it looks like there is some problems with commas in certain columns (37, 44, and 51).
If I open the files in Excel, Excel misinterprets these columns with a comma (even though this is supposed to be a tab delimited document).
When I try to do things like the following, it still fails to format the files in a way that SQL can read. removeCommasInColumns[rowList, colsList] := MapAt[StringReplace[#, "," -> ""] &, row, List /@ cols] dataworldevents = Import["mypath\20201114.export.CSV", "TSV"]; processedData = removeCommasInColumns[#, {37, 44, 51}] & /@ dataworldevents; Export["mypath\yourfile.csv", processedData, "TSV"];
This is the bulk upload query I'm using: dbQuery = "BULK INSERT worldevents FROM 'mypath\20201114.export.CSV' WITH ( FIRSTROW = 1, FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n', );";
Any help would be appreciated. I'd like to import a lot of data so this seems like it would be the fastest way to do it.
Attachments:
