0

I am trying to import a file, source here and selections here (select all fields and select "Pilkkueroteltu (otsikollinen)" and then click Jatka to download), with two header rows, " as text qualifier, comma as a field separator and with UTF-8 format. I am unable to do this in Micsosoft Server SQL Management Studio. I will focus now only on the text qualifier where " does not work (only reading the first quote as text qualifier).

enter image description here

enter image description here

enter image description here

where I am unable to specify the column separator, no idea why this is occurring.

Update 1

Refresh/Reset buttons fixed the initial preview but I am getting the following preview error in the step Select Source Tables and Views later.

enter image description here

enter image description here

enter image description here

Update 2

I get the LocaleID error The LocaleID 11 is not installed on this system. (SQL Server Import and Export Wizard). I am getting the same error despite Locale/Code page settings, what is causing this?

enter image description here

How to specify the text qualifier in the MSMM?

hhh
  • 50,788
  • 62
  • 179
  • 282

1 Answers1

1

I tried to replicate your scene. Very first, I had to delete first heading entry eg : "Kuntien avainluvut 1987-2016" Please see : sample image Column delimited is: ,

Might not be accurate answer or different from something which you expect, but by applying above settings, I could import data through SSMS2012

edit : based on comments.

Here is the detailed steps : settings

next, column delimiter

next, you need to change column width of first column as it gave me data truncate errorchange column width next, success

I have also got a dtsx package for the same, but I don't know how can I share it with you here.

Prabhat G
  • 2,974
  • 1
  • 22
  • 31
  • You can use the `Header Rows to Skip` option with `2` to skip the first two rows, it should work. Does it work for you? What is your text qualifier? Quote "? – hhh May 17 '17 at 10:42
  • 1
    Text qualifier is quote ", that's right. And yes skipping 2 header rows works too. Make sure to click reset column and refresh button(as shown in your image) after making changes. – Prabhat G May 17 '17 at 10:53
  • I got the initial preview working but the preview later does not work. I added a screenshot to the question, what is causing it? I changed the data types of years 1987-2016 `numeric [DT_NUMERIC]` and the strings to `Unicode string [DT_WSTR]` but the error, ideas? – hhh May 17 '17 at 13:04
  • Can you please share the exact file you have downloaded ? I will try to replicate here – Prabhat G May 17 '17 at 13:07
  • [This is the selection](https://i.stack.imgur.com/NwQSk.png) I did [here](https://pxnet2.stat.fi/PXWeb/pxweb/fi/Kuntien_avainluvut/Kuntien_avainluvut__2017/kuntien_avainluvut_2017_aikasarja.px/?rxid=444223df-f91c-4479-891f-5dcd50b983d2) where I selected all fields (all years, everything, command+A) to highlight, then select `Pilkkueroteltu (otsikollinen)` from the bottom selection box and then click `Jatka` to download the CVS file to be imported. This is the preview error I am getting [here](https://i.stack.imgur.com/sIsXI.png). – hhh May 17 '17 at 13:13
  • 1
    @hhh I managed to run with your prescribed settings....I will edit my answer. Hold on. – Prabhat G May 17 '17 at 13:32
  • I get the error ` The LocaleID 11 is not installed on this system. (SQL Server Import and Export Wizard)`, are you getting it as well? What could be causing it? – hhh May 17 '17 at 15:06
  • I moved the Locale error thing [here](http://stackoverflow.com/questions/44030923/ssms-locale-error-with-umlauts-collation-error), thank you for helping. – hhh May 17 '17 at 17:03