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 http://www.bigcity.ca/CSV.zip)

  • 3 Contributors
  • 4 Replies
  • 165 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by ferensick
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.