Submitting large amounts of data to a database

[mike55]

I have a page that currently accepts a .csv file and takes the records in that file and saves them to the database. I have a series of rules that are used to determine if each record is to be inserted or updated.

I originally chose .csv so people could access it with notepad, however I quickly found out that MS Excel to control of the file and was responsible for maintaining the file.

Since Excel did this, my users have been experiencing a large number of problems with submitting data using the .csv approach. How would others recommend doing it? I considered .xml, but again Excel takes control. Also the problem with .xml is that the user has to be familiar with xml and its structure. Given that the majority of my users are not computer literate, I can see huge problems with this approach.

Mike55.

 

[alreadyused]

i do something similar, and noticed in the past that when Excel reads a csv file, it changes the formatting, specifically messing up something with text fields. If i remember correctly, it did this just by opening it with Excel (regardless of whether you saved the file or not).

maybe consider doing this:
give them an excel file with the fields you want
also give them a small program that runs on their machine, reads an excel file they want to submit, converts it to csv and then submits it to your web.

the reason i suggest that is the flipside is theyre uploading an excel file which is much larger, and then you have the conversion burden on your machine, and your code is already written around handling a .csv file.


as a side note, although this would take educating your users a bit (and its still kinda clunky) I noticed that Open Offices spreadsheet program did not mess up the .csv formatting. Then they also have to register oO as the default viewer for .csv


ps
test what it does before going down the road i mention, becuase that might not be whats messing you up.
take your csv file, view it with notepad, then open it with excel, save it (in case i dont remember correctly) and then view it with notepad again. i dont remember if it was text fields or doubles or what, but it did something that wasnt friendly with what i was trying to do.

 

[mike55]

I agree with you on Excel messing up the data, i.e. if you have a mobile/telephone number, Excel knocks off the leading 0 if one exists. Not sure that I like the idea of having the users to access another program, nor am I likely to get approval for such an application. Will keep on searching, if I come across anything of use, I will get back to you with it.

Mike55.

 

[alreadyused]

i do two different things:
1. i have a report that is in csv format and I use that to update a SQL table. (this is the one that i noticed excel messed up, but i was only using it to view what data was in it in the beginning when writing the code.)

2. i have a program that covers big picture stuff, so i let users enter data for little stuff into an excel file and then i import that to the projects database (XML). that code is a bit bulky b/c its dealing with excel, but its ran on user machines and they dont notice a lag.

so anyway, whatever you end up deciding to do, id be interested in knowing just for curiositys sake.

if you could get management to approve it, a simple program/macro in excel could be launched from a button to at least make a clean version of the csv, and maybe even take care of the submitting for you.

knowing which columns were problematic, you could put a bit of logic on those. For example if the phone number starts with "11" then its probably missing the "0", so add it to the string before outputting to the csv. that may be oversimplifying the issue youre having, just throwing that out there though as a possibility.