Imports FM 9

[Stephen Slater]

I have a csv file that I am importing as a trial.

When I open the csv file in Excel I have 4140 rows of data with 20 columns per Row

When importing into FM I have 4576 records visible and the last 120 rows are full of incorrect data.

I suspected that the issue was with commas in the csv file, 

However, If I split the csv file in Excel into 2 .xls files and import them separately every this is perfect so I don't see the problem being within the csv file

Is there an issue in FM that only allows a certain amount of data to be imported?

 

Thanks in advance

 

 

[Josh Ormond]

The problem here is often the "format" of the CSV. If the data has a comma in it, the value needs to be surrounded by double quotes.

Quote

"some data",1,45.4,"some data, with a comma"

If the csv is created by Excel, or was opened in Excel, sometimes it's tries to do some conversion for you. Sometimes that helps, sometimes not. So you really need to look at the csv itself.

[Stephen Slater]

Hi Josh

I understand that possibility but like I said without any other changes if I Copy out half the data into a new file and them import both files all is well.

\it seems like an quantity issue to me that FM cant handle, strange one this

[Josh Ormond]

In your original post, you said you are splitting the file "into 2 .xls files". You are making a change, and then resaving them.

1. If you are saving them as .xlsx or .xls files, you have committed a format change. This is where Excel is helping you.
2. Even if you are resaving them as CSV, Excel is still post-processing the data.

My point is, the problem is likely the data. Once you determine what exactly is malformed in the file, then you can begin to figure out how to deal with that.

FileMaker handles CSV data really well, but that requires it to be true CSV and not pseudo-csv.

[Stephen Slater]

Ok Josh I will go in search of the csv file creation issue

 

 

[Josh Ormond]

If it's not sensitive data, you can post a sample here, and we can help you figure out the problem, and possibly suggest how to address it.

[comment]

3 hours ago, Stephen Slater said:

Is there an issue in FM that only allows a certain amount of data to be imported?

 

4000 records with 20 fields is practically nothing. The fact that you get more records than you expect suggests that some fields contain carriage returns which Filemaker interprets as record delimiters, but Excel does not.