Member Avatar for Alcutus

Hi,
I'm trying to create a CSV file from Excel to upload into a MySQL db.
It's for a web-based dealer locator called Lookup Runner.

When I try to import my CSV it never works. The people at Lookup Runner (hitcode.com) did something to my CSVs and they worked like a charm.

I don't know what they did, and I can't see any formatting difference between their files and mine?

Could someone have a look at the 2 attached files: OSH.csv is the file they tweaked, it works fine; Lowes2.csv is one of my many attempts to make a CSV file, and it doesn't work.

Can anyone spot the difference and maybe point me in the right direction?

(I can't even get Attachments to work! the file can be found at )

  • 3 Contributors
  • 4 Replies
  • 185 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by ferensick

Dani AI

Generated

Useful follow‑up that builds on and : treat the working OSH.csv as the canonical template and reproduce its bytes (not just the visible text). A few focused, practical checks will usually reveal the remaining mismatch between a file that "looks" the same and one the importer accepts.

First, check invisible differences: file encoding and any UTF‑8 BOM, stray control characters or zero‑width spaces, and the exact line terminator sequence (CRLF vs LF vs CR). Inspect the first bytes in a hex view (for example hexdump -n 16 -C OSH.csv) to see a BOM; compare both files with a byte compare (cmp) to find where they diverge. Second, verify field quoting and consistent field counts—an extra trailing delimiter or an unquoted embedded delimiter will shift every field after it. Third, protect data formats: force zip/postal codes and other numeric-looking columns to be text when generating the CSV so leading zeroes or date conversions don’t get lost.

Quick tools to find problems

  • Awk one‑liner to spot rows with the wrong number of fields (change FS to the delimiter you expect):

    awk -F';' 'NR==1{n=NF} NF!=n{print "bad line", NR, "fields:", NF}' file.csv

  • Small Python check that uses the CSV parser (more accurate for quoted fields):

    import csv
with open('file.csv', newline='') as f:
  r=csv.reader(f, delimiter=';')   # set correct delimiter
  n=None
  for i,row in enumerate(r,1):
      if n is None: n=len(row)
      if len(row)!=n: print(i, len(row))

If Excel is in the workflow, export rather than re-save when possible: preformat columns as text, use Import/Text wizard to pick delimiter/qualifier, and export with the exact encoding and line endings the importer expects. Finally, ask the Lookup Runner support for the importer parameters (delimiter, text qualifier, expected encoding and line terminator) and test with a 5‑row file before a full upload.

Recommended Answers

All 4 Replies

Member Avatar for cfry

Hi Alcutus,

Let me start by saying Excel often has issues with CSV. I've seen big problems creating/editing CSV files with Excel even for SQL Server (Microsoft's equivalent to MySQL)... You would think that Microsoft would at least adhere to their own standards... but, moving on.

I noticed a few issues with your CSV files.

1. Most of the columns (but not all) were separated with semi-colons (';') in stead of commas (','). You don't have to use commas for CSV (in fact '^' would make more sense in my opinion, because it's hardly ever used for actual data), but you do have to be consistent with which character you do use.
2. The number of columns was inconsistent. CSV files should have the same number of columns on every line. Most lines had an extra column which appears to be state again. Some columns had extra commas in them still - not sure what these were for.

I performed some very crude editing on the files for you (see attached), by:
1. Doing a find/replace for ';' with ',' in a text editor. This only works because you haven't used those characters in your data
2. Opening the CSV files in Excel (bad idea, but seemed to work ok this time), and scrolling down to look for fields that looked out of place. Some just had an extra, empty field inserted somewhere. The better way to do this would be to use perl or another language/method to find lines with the wrong number of commas.

Some other points you might want to consider that could cause you grief in the future:
1. Some of your Zip Codes are single numbers, while others are a range. I suggest using 2 columns, 1 for Zip Start, 1 for Zip End. In the usual case where one Zip Code is needed, the start and end would be the same.
2. Some of your state fields have '.' at the end. It's better to keep them consistent - I would remove '.' from all state fields

I could go into the CSV standards for enclosing fields in quotes, escaping quotes and commas etc., but from looking at your data, that doesn't matter at this stage.

Hope that helps,

Chris Fry
Perth, Australia
<URL SNIPPED>

P.S. I couldn't attach files in Firefox (might be a popup setting), but IE worked.

CSV_fixed_2009-07-31.zip (11.21 KB)
Member Avatar for Alcutus

Thanks for the reply!

This was my first post to DaniWeb.
--
You made me a comma separated file... but I think the way Lookup Runner is made it demands a semi-colon separator. (I could be wrong).

Also, since commas are used between, City & State in some of the entries in the Name column, it would be interpreted as a new column... unless I put all those Name strings inside quotation marks. (I think).

Both you and the support person from Lookup Runner pointed out that I had sloppy columns. Embarrassing! I must've stared at them 1,000 times.

Lookup Runner did clue me into a couple of other errors. Their probably not CSV errors, just requirements that are peculiar to Lookup Runner:
1. ZIP code can only be 5 digits. Some of mine were in the 5+4 format.
2. Countries can only be 2 digit Country Codes, not abbreviations.
3. Don't allow Mac "end of line" markers, only Windows or Unix...I thought I'd selected "Unix Line Feeds" on one of my many attempts.

Fun stuff for an aging Designer, eh?

It would probably drive me completely crazy if it weren't for sincere, help like I got from you.

Thanks!

Member Avatar for cfry

Ah right, sorry about that - didn't realise Lookup Runner implemented a customised CSV file.

Did you sort it out in the end?

Member Avatar for ferensick

Possibly this is solved already. (I hope) =)
Nonetheless, what I noticed that wasn't mentioned are the file formats.
Yes they are both .CSV however Lowes2.csv is in a MAC ANSI file format and the other is in a Windows ANSI file format. This makes a difference with the line breaks, which could affect the upload as well. Download a smart editor like Notepad++ (for windows) and something like TextMate for MAC... pretty much any editor where you can change the file format (not just the extension). If MySQL is on windows, then the MAC line breaks could be affecting the upload.

I attached the updated Lowes2.csv in a Windows ANSI format. If you edit it on your MAC, this will change. So try uploading it first without editing it.

*Sorry I just noticed that the line feeds were mentioned already. I noticed the comma thing as well. Anyway I hope these files are all sorted out now.
Cheers

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.