CSV Import skip the first two lines


#1

Hi, I hope you can help me. I have a CSV import working correctly. But I would love if it can skip the first two lines in the CSV file.

Best regards Kai


#2

Hello @kai74,
What’s the purpose of doing this? What do you have on the first two lines?


#3

Hi @Teodor

We have an App we used to register all strokes we are using when playing Golf. When the round is finished, it is possible to download a CSV file, and for some reason, they have created a blank line on the second line. And the database is complaining. It will make it much more user-friendly if I can import this file directly. I am not sure we can do it a different way, maybe skip lines with Null values.

I have made this import earlier in PHP, and it works.

This is the old code. (made by help from Google)

move_uploaded_file($_FILES[“file”][“tmp_name”], “files/” . $_FILES[‘file’][‘name’]);

$file = “files/” . $_FILES[‘file’][‘name’];

$query = <<<eof

LOAD DATA LOCAL INFILE ‘$file’

INTO TABLE resultemp

CHARACTER SET utf8

FIELDS TERMINATED BY ‘;’

LINES TERMINATED BY ‘\r\n’

IGNORE 2 LINES

(placering,player,hcp,ToPar,Score)

eof;

if (!$result = mysqli_query($con, $query)) {

exit(mysqli_error($con));

}

$message = “CSV file successfully imported!”;

$query2 = “INSERT INTO resultater (resultater.playerID, resultater.placering, resultater.hcp,resultater.ToPar,resultater.Score) \n”


#4

Hello,
You can use simple condition step, to check if the CSV row has a value. For example check if the ID(or whatever your identity column is called) column if the field has a value. If it has a value run database insert else, do nothing:

So under condition put the insert into the THEN steps, and leave ELSE steps empty.


#5

@Teodor

Perfect Teodor. I will test… Thank you for taking the time to answer my question


#6

Hi @Teodor

It is not working. It fails on the blank line. Maybe I am too green to understand this. The file looks like this:

;Player;HCP;To Par;Score
1.;Kalle Dyngbo;27;-3;39
T2.;Petter Reinstrup;16;+1;35
T2.;Karsten Paulsen;21;+1;35
4.;Kalle Banan;21;+2;34

I insert all the records in a temp table and enrich the table with some values from some custom selected. And it works well.

I have tested with the condition on all Fields. :slight_smile: We do not have an ID identity column, the only place we have a unique value is the Player name. During an insert, I remove all the “.” and T from the first column.

Best regards Kai


#7

It doesn’t need to be an unique field, you just need to check if the field is empty.
Can you attach a sample csv file here, which I can test with?


#8

Yes @Teodor
:slight_smile:
Final_Results_test.csv.zip (704 Bytes)


#9

Thanks, I will check that.


#10

I just tested with the settings i provided in my earlier post and it works as expected. I imported only the expected fields:

So these are the steps needed:

  1. Make sure to define your csv columns! (i see there you have a first column without a name so i defined it as well). Define them EXACTLY as they are in the CSV - text case matters.
    Do not forget to turn the Contains Headers option ON - this will skip importing the first line!
    Make sure to set your delimiter to ; as in your csv this is the delimiter:

  1. The repeat step repeats the csv step:

  1. Inside the repeat step we have the condition checking if the {{Player}} field has a value. Select this through the data picker:

  1. And inside the Then steps we have the insert:


#11

Hi @Teodor
Thanks for all the help. It is working the way you are doing it. The only problem is that I need the first “blank” column. Can we fix that somehow?