1

I am trying to implement a bulk import via bash shell (Windows) of multiple CSV files in MySQL database. I am running into the issue that my CSV files (500+) have two different number of columns.

CSV type 1

No,Latitude,Longitude,Name,Altitude,Description,Symbol,Filename
1,-26.976823,21.836022,"1608",912.7,"1608","Flag","W80716pmNATV"

CSV type 2

No,Latitude,Longitude,Name,Altitude,Symbol,Filename
1,-26.977080,21.833260,"0649SB",902.0,"Flag","W81018amDH.mps"

How can I manipulate:

LOAD DATA INFILE ' VH110131pmBES.mps.csv '
INTO TABLE `gpsdata`.`import`
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES (@dummy, `Latitude`, `Longitude`, `Name`, `Altitude`, @dummy, `Filename`)'

to incorporate both files in one bash code? I do have a unique ID field at the beginning autoincrementing.

livibetter
  • 19,832
  • 3
  • 42
  • 42
Timvink
  • 27
  • 7

1 Answers1

0
  1. Go for the wider table (type 1) always
  2. remove the column names in you LOAD DATA... command after ...IGNORE 1 LINES
  3. Run the Imports
  4. check all new tables in mysql for empty columns Filename -> These are type2 formated tables
  5. For type2 tables only: Move content of Symbol to Filename, move content of Description to Symbol, drop column Description
Benvorth
  • 7,416
  • 8
  • 49
  • 70
  • Ah, so you mean I shouldn't try to filter out up front, but run it through a "temp"table from which I edit content and put in the final database? This is a lot of steps for one variable that changes, is there no easy way of counting columns of the csv and making a if function in bash? – Timvink Apr 17 '15 at 06:35
  • yes and no, I don't think so. You can ensure that both types will be delivered in the same Format, eg with empty columns. But if you can't influence that you will have no choice manipulating the `LOAD DATA...` statement – Benvorth Apr 17 '15 at 16:06
  • Why not just read the first line of the file by Batch? See [here](http://stackoverflow.com/questions/130116/windows-batch-commands-to-read-first-line-from-text-file) - and then run a Different `LOAD DATA...` according to the result... – Benvorth Apr 17 '15 at 16:09
  • In the end I've bypassed this problem by using a php approach in importing the files. Reading all filenames in, opening one by one the csv, reading the first line and adjusting the insert statement accordingly. – Timvink Jun 25 '15 at 17:08