Import a csv with mis-matched column counts

Wappler General How To
#1

Hi all,

I am trying to import a csv file which is a call report supplied by our telephone system.

Previous (other types of reports) csv’s imported fine (with a bit of conditional logic) but this one is not importing with the error -

parseCSV: colomns do not match. keys: 12, values: 1 at line 3

Checking the csv it is correct, on line 3 (and 4) there is only one column. All the other rows have the correct number of columns (the last row in the below example is the header row):

,,Call Report,,,,,,,,,
Type: Call Reports,,,,,,,,,,,
All Calls: From 6/8/2023 12:00:00 AM To 6/8/2023 11:59:59 PM
Caller ID: Any number
Destination: Any number,,,,,,,,,,,
Call Time,Caller ID,,Destination,Status,Ringing,Talking,Totals,Cost,Reason,Play,

I don’t want the first 6 rows since it is all extra information I don’t need.

I have checked all the exports of this type and they all have this issue.

I have also raised this odd formatting with our telephone system supplier but I don’t expect any changes to happen quickly.

Since parsing the csv gives an error, is there a way in Wappler to remove those lines before the “Import csv File” step or to force Wappler to ignore the rows or column count?

Thanks,

Paul.

#2

Hi Paul,

I haven’t done CSV processing in Wappler, but here’s an algorithm to fix the CSV to have the right column count:

  1. Use Set Value with the split formatter to split CSV file contents (string) with separator \n (returns Array)
  2. Iterate each line (use Repeat step on previous Array)
  3. Split line ($value) with , (comma), and check the length (Integer)
  4. Calculate the difference between the expected length, and the actual length
  5. Use Repeat step to repeat difference times, and in each iteration append a comma to the line in question
  6. Use a Set Value with .flatten formatter to flatten the array
  7. Outside the primary Repeat, join the returning array of the Repeat with \n
  8. Perform CSV parsing

Hope this helps you somehow, despite not being beginner-friendly instructions (not so straightforward implementation in Wappler steps). CSV with weird format causes you to deviate from the standard :woozy_face:

Try to do each instruction and feel free to ask for help in the step you get stuck. The instructions might not be precisely written, but should serve as basis for the implementation

#3

Hi Apple,

Thanks for the instructions.

I couldn’t figure out how to read the file into a variable Set Value so created an extension to get the contents and return it as a string (based on this - Server Side Read & Write File - but converted to node.js).

Then worked through your steps but then couldn’t get the CSV Parse step to use the string, it wants a file.

Then in my testing I eventually figured that since I didn’t need the lines at all I could just remove them.

I changed my custom extension to read the file contents, remove the offending lines and save it back in place so then the CSV parsing works.

Not really in the spirit of no code but works in my specific case. :slight_smile:

Also, trying and failing is my preferred method of learning so this exercise was totally worth it.

Thanks Paul.

2 Likes