Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Re: Unable to load entire CSV file into DB

by cchampion (Curate)
on Nov 06, 2004 at 17:22 UTC ( #405787=note: print w/ replies, xml ) Need Help??


in reply to Unable to load entire CSV file into DB

Something for you to investigate:

  • Are you sure that the file is uploaded completely?
  • Are you checking for database errors?
  • Have you read Before asking a database related question ...?
  • Did you try that SQL statement outside your script? I mean, manually, from the mysql monitor?
  • Does the table structure correspond to the CVS structure?

If the answer to all the above questions is "yes", then I am prepared to accept that you might have a Perl problem. Otherwise, I should address you to a a more appropriate place where to ask your question.

Moreover, you are asking for a database related error, and then you are showing us an uploading script.

Is that relevant?

If not, why don't you skim the example to the only thing that matters?


Comment on Re: Unable to load entire CSV file into DB
Re^2: Unable to load entire CSV file into DB
by bradcathey (Prior) on Nov 06, 2004 at 17:52 UTC

    Thanks for the tips.

    Are you sure that the file is uploaded completely?

    Yes, in all cases, it's all there

    Are you checking for database errors?

    I'm using { RaiseError => 1} and $dbh->trace(2) and no errors.

    Have you read Before asking a database related question ...?

    I have, but just re-read it.

    Did you try that SQL statement outside your script? I mean, manually, +from the mysql monitor?

    Did not. Working on a remote shared web server.

    Does the table structure correspond to the CVS structure?

    Dead on!

    Again, some of the file is importing. It seems like there is some hidden character in the CSV. Per your suggestion, I will ask in a more 'targeted' forum. Thanks!


    —Brad
    "Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton
      Did not. Working on a remote shared web server.

      Don't you think that this very step could tell you in which direction to look?

      I would try to do the testing in a local machine. Installing MySQL is not rocket science and your table does not seem to have a million records.

      So, instead of guessing, why don't you test it?

      I expect the file format will be the issue, quite possibly the line endings. The first simple thing in your upload is to do this:

      $buffer =~ s/[\r\n]+/\n/g;

      to standardise all your line endings. You could use \r if you want but \n is the default, the standard .... and it's less typing in your LOAD DATA.

      If that does not work then I suggest you use perl to read and validate the uploaded file before you pass it to mysql. I too suggest trying to load the file manually. You could download the file and try loading it into a local MySQL DB if you don't have access to the remote one.

      cheers

      tachyon

        Great suggestion, I've written this in as one more safeguard. However, I don't understand all that I'm seeing when I check for ascii or hex values. Here's a dump of a file that looks like this:

        ,AL,2 ,AK,4

        in ascii

        A--65 L--76 ,--44 2--50 --13 ,--44 A--65 K--75 ,--44 4--52 --13

        What is the blank spot? Even if it's a new line, shouldn't it have a value? Just curious.


        —Brad
        "Don't ever take a fence down until you know the reason it was put up." G. K. Chesterton

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://405787]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others scrutinizing the Monastery: (5)
As of 2014-12-21 01:28 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (100 votes), past polls