Odd SQL Server import problem

[scaryjim]

Sincerely hope a clever hexite can help me out here, as I'm pulling my hair out with this one.

I have a CSV file. It contains data (well, duh ). A couple of the columns have "missing" data - i.e. blank values. When I load the file into the Import Export wizard (using SQL Server management studio express 2008), and set up all the correct datatype, the data preview clearly shows all the lovely blanks that I expect to be in the file.

I have created a table to put my data in. It has the two columns that contain blank values set to allow nulls. It had everything set to appropriate data types. I can map the columns during import, and when I preview the data it shows me all my lovely blanks.

I can successfully import the data into my table.

Now, perhaps I'm just being naive, but when I import blank values from a CSV into columns which are set to allow nulls, I expect to get null values in the columns. But I'm not - I'm getting zeros. I've checked and there's definitely no default value for those columns, the columns are definitely set to allow nulls, and as I say all the data previews during import show the blank values.

So, anyone got any idea why SQL server hates me today? I really can't see anything else I can do differently to get this import working...

[Tattysnuc]

Is it a text field or a number field? If it is an int or double try changing it to a varchar.

[shaithis]

The GUI based import does a few things like that depending on datatypes used. It's a pain.

BCP solves all the issues but a lot of the time it's faster to use the GUI import and then run a cleanup command or two.

I.e. if you have no zero values, you can update all the zeros to nulls.

Or, if you are a coder, you can write a little bit of vbscript to handle it for you on the data transfer.

[scaryjim]

I ended up sidestepping it in a most ridiculously convoluted way - I used the original SPSS file rather than the CSV export, imported that into Access, that preserved the nulls, then I imported the Access into SQL Server. What a palarver *sigh*

Tatty: it's all numeric fields. If you try to persuade the import wizard that they're text really and it should convert them, it throws its toys out of the pram about loss of data precision. And whilst I don't necessarily need the fields to be number in the database, it makes a lot more sense of the filtering options etc. if they are. Odd thing is I'm sure I didn't have this problem with the last dataset for this project, but I'm now wondering if I cleared all the nulls out of that one first...

The big issue with this job was that one of the fields used 0 as a meaningful data item, soit was trashing the meaning of the data, not just the structure *sigh* I have SQL Server sometimes...

[Tattysnuc]

I ended up sidestepping it in a most ridiculously convoluted way - I used the original SPSS file rather than the CSV export, imported that into Access, that preserved the nulls, then I imported the Access into SQL Server. What a palarver *sigh*

Tatty: it's all numeric fields. If you try to persuade the import wizard that they're text really and it should convert them, it throws its toys out of the pram about loss of data precision. And whilst I don't necessarily need the fields to be number in the database, it makes a lot more sense of the filtering options etc. if they are. Odd thing is I'm sure I didn't have this problem with the last dataset for this project, but I'm now wondering if I cleared all the nulls out of that one first...

The big issue with this job was that one of the fields used 0 as a meaningful data item, soit was trashing the meaning of the data, not just the structure *sigh* I have SQL Server sometimes...

I know your pain. I too am banging my head against a wall trying to import an excel spreadsheet in with a simple product hierarchy in it, but I want to save the SSIS package so that the hierarchy, which isn't part of our ERP, can be maintained, and then incorporated into our SSAS database.

First tried importing it, failed. Wrong datatype, so I put the file on the server as a flat file, with Tabs delimiting. Imported, but cannot change the column names from Column1, column2 etc.

Version 3 finally imports, and I set the first column to varchar(2), and the rest to Varchar(50), but the import routine cannot find the table, despite choosing and editing the SQL. GRRR. I could really hate MS sometimes....

[scaryjim]

s'ok, the other developer here is busy converting all my int's to varchars anyway, on the basis that it takes less storage

The most amusing thing? He's used nvarchar not varchar, so he is *guaranteed* to use at least as much storage for every field. I need to have serious words about division of labour and not redoing everything someone else does just because it's not how you'd do it...

[Tattysnuc]

I'm still very much a SQL SVR 2k8 virgin, so that's gone straight over my head. not even sure what an nvarchar is as opposed to a varchar - I should look that up.

Needless to say, the SSIS package had worked, but my data somehow contained exactly double rows, and that was breaking the primary key rule I'd defined on t he table. I cleaned the data and it's running sweetly now.

I've also found the location of the SSIS packages that are saved in SQL Server 2008 R2 64bit - dbo.sysssispackages and managed to clean up after myself. Clever me I thought - it's probably in a 101 lesson, but I swear I looked there earlier and couldn't find them!

Life is good again, just in time for the weekend....

[scaryjim]

Good stuff

nvarchar is unicode, rather than ascii, so takes 2 bytes of storage per character rather than 1. And both nvarchar and varchar use 2 bytes of additional storage to handle the variable length thing. So an nvarchar uses 4 bytes of storage to handle a single character; a varchar uses 4 bytes of storage to handle 2 ASCII characters, and an int uses 4 bytes to store any number up to 2^31ish! So his storage optimisation breaks down for any number larger than 99

Get stuck in to SQL Server - I whinge about it a lot but it's very powerful, and Microsoft provide some decent tools for working with it. I hate to say it in a lot of ways, but I'm much more productive working with Microsoft technologies than I ever have been working with open source tools.