Microsoft SQL Connector - Adding empty spaces in columns from imported double quoted CSV file

Questions
1

I have a question about the Microsoft SQL connector, Insert function.

When using a CSV file file as a source to import into SQL, the values imported in the database have empty spaces.

image
image952×154 7.57 KB

If we look at the source data, no empty spaces are present in the source JSON from the Convert to Spreadsheet step:

image
image668×746 18 KB

I have noted this issue seems to only happen when values, in the source file, are in double quotes. Here is the sample data:

“Template Name”,“Org Unit Code”,“Hiring Leader User Sys ID”,“Hiring Leader Name”,“Job Req ID”,“Job Title”,“Work Location City”,“recruitingV2#RCM_JOB_REQ#STATE_PROVINCE”,“New / Replacement”,“Reason / Justification”,“Schedule”,“Job Status”,“Company”,“TA Consultant User Sys ID”,“TA Consultant Name”,“Employee ID”,“Candidate ID”,“Candidate Name”,“HireType”,“Source Details”,“Start Year”,“Start Month”,“Start Date”,“Hired On”,“New Hire Cost Centre”,“Is this a Cutover Requisition”,“Time to Hire”,“SAP Job Title”,“Work Location (CLLI)”,“RSU”,“Candidate Work Location City”
“Retail Requisition”,“12345678”,“C12345678”,“Test Test”,“12345678”,“Store Test”,“Test Town”,“12345678”,"New ",“Test Comments bla bla bla bla…”,“Regular”,“Full Time”,“C12345678”,“Test Test2”,“12345678”,“Test Test2”,“Internal Hires”,“Recruiter Sourced”,“2022”,“1”,“1/1/2022”,“1/13/2023”,“12345678”,“120.62”,“Store Test”,“TM, Test Mall(HGTHHGFT)”,123456,“Test Town”

I do need the double quotes in my file as certain records may be multiple lines.

Have you encountered this issue before and have a possible cause and fix ?

Thanks,

Christian

2

Hey @cmorneau,

That is odd, I would expect us to insert the data as you see it in n8n :thinking: If you query the data in SQL does it display correctly or does it only have the spaces when you export it?

3

Hello Jon,

The spaces are present when I export data, may it be from Microsoft SQL Server or n8n.

Working correctly aka no spaces with a file without double quotes.

Thanks,

Christian

4

Hey @cmorneau,

That is interesting, I guess if the data is correct when doing a select in SSMS or similar tools then it might be something funky with the export.

5

Hello @Jon,

Feel free to try to recreate it with my provided data. It seems to be in the correct format and encoding, just having double quotes. It’s only in this scenario of double quotes in the source file that it happens.

Thanks,

Christian

6

Cause found, fix applied !!

I was using nchar in my columns instead of nvarchar. I just dropped and recreated my table with all variables in nvarchar, no empty spaces anymore.

Closing issue.

Christian

2 Likes
7

That is a nice find.

1 Like