jab43 has asked for the wisdom of the Perl Monks concerning the following question:

I am working on a project to load Excel data into a database. I am converting the Excel files into .csv files and using sqlldr to insert the data into the database.

I have just discovered that although an Excel file saves correctly as a .csv file, pertaining to one row per line, when I read the data line by line to add further columns for each line multiple line cells are wrapped in quotes but contain newlines. So as I read the file line by line it breaks apart this cell into new rows. I don't want that.

I haven't discovered a way to read line by line but ignore new lines within quotes. Or I am open to any other way to manipulate the data to add it to a database. Here's an example of a line from the csv: 

MM01,aluminum,AMS-QQ-A250/11,Raiser,N/A,W6548,Distributor,MiTech,,"PO#
+F63254PO#H54675PO#G87954"

[download]

So I basically need to find the true end of the line. Thanks!

Replies are listed 'Best First'.
Re: Ignore newlines within quotes while reading a line in a text file
by choroba (Cardinal) on Aug 09, 2013 at 20:15 UTC
    Use Text::CSV to read the files. It is what is was designed for.
    لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]

      I looked into that module but I am limited to using the basic Perl installation so I cannot use that module.

[reply]
        Good luck copying the source of the module into your programme, then.
        لսႽ† ᥲᥒ⚪⟊Ⴙᘓᖇ Ꮅᘓᖇ⎱ Ⴙᥲ𝇋ƙᘓᖇ
[reply]
        I looked into that module but I am limited to using the basic Perl installation so I cannot use that module.

        Then that is the real problem you should be resolving! You don't even need to be system administrator, you can install modules as a regular user.

[reply]
Re: Ignore newlines within quotes while reading a line in a text file
by marinersk (Priest) on Aug 09, 2013 at 23:00 UTC
    I will get with you later this evening. Whent Text::CSV was first written, it did not handle the CSV standard properly. I was not in a position to contribute to the development effort, and needed a solution in a hurry.

    Practical upshot: I wrote my own CSV module which handles the standard and was written with built in test cases to help ensure it stays that way.

    I would be happy to donate my code to your effort if it will help -- but I need to travel immediately and won't have to time to post until later tonight.

    Under normal circumstances I would never recommend my code over a CPAN module, but this is an odd confluence of two events which should have been so rare as to never meet.

    Life's like that sometimes.

    Chat soon.

[reply]
      Apologies; I am still mobile. Expecting to be home within the hour.
[reply]
        Nope -- still mobile. Will check in soon.
[reply]
      Have left you a private message here on PerlMonks with link to the module, such as it is. Good luck, and holler if you have any questions.
[reply]

Back to Seekers of Perl Wisdom