export to postgreSQL

I was having trouble exporting a csv file that has 189,000 rows to postgreSQL. I think the data is not consistent and this includes what look like null or blank values. I used another kind of workflow. I opened the excel file in power bi, using data -> from table range, to move to power bi query. There, using replace, I replaced all null and " " with 0 and saved the result back to excel and then save to xlsx. knime’s excel reader then read the file. One column needed a string to number to conversion, to decimal. The posgreSQL connector connected to my postgres table. And the DB writer wrote the data to postgreSQL, where I thought I needed to use SQL to query it. I course I wonder if all of this could be done with Knime? But the initial “clearning” of the data was beyond my capacity with respect to Knime. Would anyone know how to do the clearning, in knime? read excel.knwf (17.1 KB)
Errors in the workflow that I do not understand are, for the excel reader:

  1. WARN String To Number 4:4 Values in 19332 cells could not be parsed, first error: ’ - ’ (RowKey: Row93, Position: 47)
  2. WARN String To Number 4:4 Problems occurred, see Console messages.
  3. WARN Excel Reader (XLS) 4:1 DataSpec generated by configure does not match spec after execution.

and for the String to Number:

  1. WARN String To Number 4:4 Values in 19332 cells could not be parsed, first error: ’ - ’ (RowKey: Row93, Position: 47)
  2. WARN String To Number 4:4 Problems occurred, see Console messages.

Hey @Selster,

With regards to the errors:

  1. You could look at using a regex replace in the String Manipulation or Column Expression Nodes to replace the special characters before converting to a number.
  2. You could look into validating the table, before performing any manipulation with the Reference Table Validator

Hopefully that helps!

Regards,
Wali

2 Likes

Thank you for those suggestions. I think the real problem starts earlier. the excel reader node will not read the entire file, and I have tried all of the options. Same for the excel csv reader. The line reader will read all rows but everything in one column, where of the original commas is separated by a comma, And I don’t know how to parse it, in knime, so each row looks like this:
Smith, Bob, 123 street, 29 years old, etc.

Hello @Selster,

can you share your Excel file? That way others can give it a go.

Br,
Ivan

Sure I can share it. But it’s 185k rows x approx 40 columns? I have further realized that the knime excel reader works just fine, its just a little slower; the knime file reader, works only for the csv, it stops somewhere when reading the xlsx, of the same file; and the line reader will put data all in one column, separated by columns. I will think about how to share it. Thanks, Steve

Hello @Selster,

to share data you can either put it into data folder within KNIME workflow directory either execute reader and not reset workflow while exporting it either attach it here on forum as you did with your workflow above. Anyways check this topic here for more about sharing reproducible workflow examples:

Br,
Ivan

2 Likes

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.