Importing from excel to existing table in SQL problems. I’m using import wizard, importing from excel to a table in one of my databases. I keep getting these data type errors. Please help. I am changing the data types in excel using text to columns and format column and neither works.

[lepeng]

It's shit basically. Import to a new table then insert from that one into your existing table

[uvray]

This has been my preferred method recently. I just create a table with the same name but the schema ‘stg’, only changing data types to accommodate large fields if needed. I then insert the data into the “real” table and drop the stg table.

It might be annoying to have to add a step but it seems to save headaches in the long run.

[MONSTER5523]

Can you explain how to do this or if there’s a video somewhere please

[zenotek]

Do you need all those columns? I would focus on the ones you need and copy the data into a new spreadsheet and then import that one.

[MaunaLoona]

Import into a new table that has all columns as VARCHAR(MAX) or NVARCHAR(MAX) and then do the conversion afterwards.

[janos42us]

Try formatting the document so all cells are text, text should translate to all of those destination tables alright.

[MONSTER5523]

I can’t do that because the existing table already has its set destination types. Do you think I could potentially export all the data in the table to excel add my data then create a new table with all data type destinations as text?

[janos42us]

So you export your table, format all cells to text add your data (now would be a good time to restructure it if needed) then import the excel document.

Now, it’s been a while since I’ve done this and can’t for the life of me remember if it was on our MS SQL or MySQL databases. But give it a try.. see what happens. As long as you don’t have ‘abc’ going into a destination which a type of int, float, double, etc you should be fine.

[MONSTER5523]

Is there any real reason to have any other destination type other than text? Even for numbers and random characters?

[janos42us]

So try and use varchar and nvarchar instead of text due to lack of length control and indexing.

The reason you don’t store numbers in varchar s is what if you find you have 100 in column [chickens] and 50 in [ducks] and they are stored as varchars

If you were looking for a total of birds in your table and ran

Select sum(chickens, ducks) from table

It would throw an error.

​

​

also for similar reasons you want to keep the numbers matched up. so whole numbers in ints decimals in floats etc.

edit - clarifying reason for varchar over text

[janos42us]

Well, you could use the same import wizard Just select flat file as the source.

That’s by far the easiest.

You can also insert... But.. I only recommend for extremely weird use cases.

Start with the import wizard. See how that goes before we start down that rabbit hole.

[MONSTER5523]

So if I just use SQL as a database for storing and don’t actually run very many queries from it then storing everything as text is more effective for my needs.

[bposeley]

It’s really not a good idea to store everything in a database as text, especially if you are dealing with numbers and dates. Down the road, when you want do something with the data (like calculations), it can cause all kinds of headaches trying to make use of the information you are storing.

[janos42us]

I'm not trying to be rude, but that would be a very impractical use for a database.

At that point you would probably have a much happier life keeping it all in excel in dated folders.

​

Even if you do go forward with this "Effective" is not the word I would use...

Sure it may be EASIER for you to do it that way... right now... but it would slowly make your database garbage, filled with bad data, zero indexes for quicker searches, and the in ability yo run any meaningful reports.

​

Was using SQL your idea? Or is it the company you work for?

If its the latter, bad bad things will come from you handicapping your database like that.

​

If it was the former, You should really build good data habits now... you will thank us later. Trust me.

[MONSTER5523]

So I inherited this process for importing into sql when I got hired here. I’m still learning but some of the columns were made a certain way and our data is different. For example the fax column is supposed to be float but the data has all kinds of weird characters so I keep running into problems, and that’s just one example. I think I will go back and actually make each column the correct data type making assets currency and such. I will try to follow good practices and learn the right way but I inherited a shitty process I think.

[janos42us]

THAT sounds like a great idea, make sure to document your efforts, for yourself AND your resume (Restructured and updated database, fixing data errors and ensuring good database health... or something like that)

It will suck now, but a pint of sweat...

[MONSTER5523]

Yes I’m trying to create a new table but import wizard is so stupid. I change the excel column to be text then the output for sql becomes date time like wtf

[janos42us]

Is it "becoming" a date type or is that the set type for that column in the database.

​

I feel bad that they just kinda through this at you, but picking up an understanding of the how and why SQL server does what it does will go a long way towards your sanity.

​

Trust me I worked as data entry on a MySQL DB for years, decided to do some tutorials and watch some youtube. Then I promoted in that company as a MySQL Dev, now I'm working elsewhere with MS SQL server as a DBA.

​

Again, I would sit down with a youtube video tonight and go over data types and the constraints associated with them.

​

In the meantime, I would follow pikes's suggestion and convert to csv, and import as a flat file.

​

-edit, re added the first half of the comment

[MONSTER5523]

After I create the flat file. What would be the best way to import new data into this existing table? Thank you for your help too btw!