• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Testing CSV Import fails repeatedly
#1
Here's the contents of the simplest .csv I've tried to import into MSP:

titles;pages;
Audience;77-80;
Back Doors Crying;06-10;


I've also tried a version without the semi-colons at the end of each line
titles;pages
Audience;77-80
Back Doors Crying;06-10


It appears that MSP throws numerous errors, but only lets me see the final one: "A pages column was not found . . .."

I've also tried a version with singular first row titles and again with capitalized first row titles. No luck. In those cases, the error was something like "None of the columns in the CSV are valid."

Can someone explain what is wrong with the pages column in this simple .CSV? How about how "None of the columns" in these simple CSV's could be valid? This is MADDENING!

The .PDF I point to upon prompting is a perfectly valid .PDF with bookmarks. I had previously imported it in a variety of ways: as a simple file import of the entire pdf (essentially as one "song") and also a PDF bookmarks import. After each successful attempt, I deleted all the created songs & related files (within MSP), so each of my unsuccessful .CSV inputs was into a clean Library to which these 2 songs and the PDF containing them were unknown. I need the CSV import to work consistently. Please help me solve this.
Reply
#2
The title column needs to be singular: title

So it should be:

title;pages
Audience;77-80
Back Doors Crying;6-10

Give that a shot and it should work as long as you have a PDF named the same thing as the .csv file in the same directory. I tested those exact three lines in a file and it worked.

Mike
Reply
#3
(03-08-2020, 04:10 PM)Zubersoft Wrote: The title column needs to be singular: title

So it should be:

title;pages
Audience;77-80
Back Doors Crying;6-10

Give that a shot and it should work as long as you have a PDF named the same thing as the .csv file in the same directory. I tested those exact three lines in a file and it worked.

Mike
Funny that it would be the "title" column that needed to be singular, but the error message referred to the "pages" column! I'll try this one more variation. In the meantime, is there a requirement for the "encoding" of the simple .CSV file? My database export defaults to "UTF-16" but I've tried others to see if that's the problem.

Just tried another one with "title" instead of "titles" and now the error is "There is no title column and one is required." IRONIC, huh? Tried title and Title and the result is the same.

BTW, I see that you removed my leading zeroes in the pages field. Is that required? Most of my fake books are over 100 pages so I've used 3-digit numbers for the ranges. This particular PDF has less than 100 pages, but is extracted from a larger songbook that has over 100 pages, so I stuck with the 3-digit numbers even though there are only 80 or so in the PDF.
Reply
#4
(03-08-2020, 04:10 PM)Zubersoft Wrote: The title column needs to be singular: title

So it should be:

title;pages
Audience;77-80
Back Doors Crying;6-10

Give that a shot and it should work as long as you have a PDF named the same thing as the .csv file in the same directory. I tested those exact three lines in a file and it worked.

Mike
When you say same name in same directory, are you referring to the non-MSP directory to which I'm directing MSP for the CSV import? Or do I need to move both files somewhere else before I identify them during the CSV Import. I thought I had read that you had gotten rid of all those requirements, especially where the program prompts me for the 2 files separately.
Reply
#5
I would leave the file in UTF-8 encoding to ensure compatibility. Leading zeroes is fine - my code should strip those when it parses out the integer values. If you would like me to send you the CSV file I just created that works so you can test with it, I'm happy to do that.

With the Windows 10 version, if you import a csv file that is outside of the MobileSheets storage location, it will prompt you to pick the PDF that goes with that CSV file. If you import a csv file that is inside the MobileSheets storage location, it will look for a matching PDF for that csv file and will automatically use it if one is found. 

Mike
Reply
#6
If you want to email me the file you are working with, I'm happy to take a look at it and test it out myself. You can also find lots of examples of files from other users on the forums: https://www.zubersoft.com/mobilesheets/f...php?fid=29

Mike
Reply
#7
(03-08-2020, 05:09 PM)Zubersoft Wrote: I would leave the file in UTF-8 encoding to ensure compatibility. Leading zeroes is fine - my code should strip those when it parses out the integer values. If you would like me to send you the CSV file I just created that works so you can test with it, I'm happy to do that.

With the Windows 10 version, if you import a csv file that is outside of the MobileSheets storage location, it will prompt you to pick the PDF that goes with that CSV file. If you import a csv file that is inside the MobileSheets storage location, it will look for a matching PDF for that csv file and will automatically use it if one is found. 

Mike
I've solved my simple Title;Pages only CSV import by playing around with encoding and BOM. I've achieved success with 2 different CSV export settings from my database: (1) declaring NO encoding; and (2) declaring ISO-8859-1 coding. I've examined both resulting files in a hex editor and they are free of BOM.

By contrast, every other encoding I tested (INCLUDING requested UTF-8 or UTF-16) resulted in a BOM (EF BB BF for UTF-8 or FF FE for UTF-16); no file with a BOM of any kind was accepted by MSP.

From my own readings, "UTF-8" can be with or without its identifying BOM or with it as the 1st 3 bytes of a text file (EF BB BF). I've been experimenting and my .CSV exporting software exports a .CSV with the BOM when UTF-8 is requested. By contrast, when ISO-8859-1 is requested, the .CSV has no BOM and if I fail to request any particular encoding, the same result. From what I read, UTF-8 encoding WITHOUT the BOM is often necessary not to confuse "older" software. Ouch! Smile  So you might want to consider adding to the Win10 manual that the user should ensure that any CSV file used for import does NOT have a BOM; specifiying UTF-8, without more, is apparently not good enough and, in fact, specifying ISO-8859-1 produces a better result. Or, of course, you could programatically strip any BOM before parsing the text--might prevent insanity infecting users with no idea what a BOM is or how they got one! Smile

Now I need to find out what else is wrong, if anything, with my larger CSV with lots of columns and lots of rows. Are there ANY MSP fields or groups that are not now eligible for CSV Import? Is the Win10 manual still up-to-date about the allowable import fields? Thanks again.

- Kevin
Reply
#8
I've added code to strip the BOMs from UTF-8 or UTF-16 files. My code already handled those encodings correctly, but didn't handle the BOM which messed up the identification of the columns in the first line. The Windows 10 manual is up-to-date as far as the allowable fields. 

Thanks,
Mike
Reply
#9
(03-09-2020, 04:02 AM)Zubersoft Wrote: I've added code to strip the BOMs from UTF-8 or UTF-16 files. My code already handled those encodings correctly, but didn't handle the BOM which messed up the identification of the columns in the first line. The Windows 10 manual is up-to-date as far as the allowable fields. 

Thanks,
Mike
That's great!! Thanks! I'm sure we've saved someone else a miserable and unproductive Saturday! Is there any MSP field other than Tempo that can accept only a specific data type (.e.g. numeric in the case of Tempo is the only one I see in the manual). I tried to use that field for a combination of numeric (e.g., 120) and text (e.g., Slowly), shoving them together with a | [bar] separator. MSP rejected all data in that field, including the 000 I had inserted to the left of the bar for songs that did not have a numeric tempo specified.

P.S. For future readers, I presume you meant UTF-8 or UTF-16.
Reply
#10
Difficulty requires a number between 0-14, rating requires a number between 0-5, and duration requires the number of seconds the song is.

Mike
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.