August 9, 2021 at 4:53 am
I'm not sure why Jeff keeps talking about the version of SQL Server. We are not talking about BULK INSERT here.
Because that import flat file thing has been upgraded to behave a whole lot better with CSVs.
The other reason is as I stated... the GUI tool if fine for doing a one off a time or two but if you need to do it more often, automate it. BULK INSERT does work a treat now.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2021 at 5:01 am
Jeff Moden wrote:Understood on the SSIS thing but the Op wasn't using SSIS. For that matter, I typically try to make it so that I never have to use SSIS anywhere. .
he is using the " I'm using Import Data. Flat File Source as my means of importing the data. " ' this is SSIS for all intents and purposes.
It will even create a SSIS package that can be executed on the server.
Ah... understood. I do know that they are under the covers but, for some reason, I don't equate the two in these types of discussions. I seriously try to avoid both.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2021 at 3:44 pm
Hi Guys,
I am having issues trying to import data from CSV files into existing tables in my database. The first row is field names, the values are separated by commas and the values are enclosed in quotes. The files were generated by an MS Access Query Export. I'm using Import Data. Flat File Source as my means of importing the data. Some of the files import without issues while others are a no go despite the fact that everything looks fine as I step through the import process. I'm not sure what the issue is. Importing data this way is a common practice and should not be a hair pulling exercise. I would appreciate any help you can provide. I've included two files as an example.
VS, I can sympathize with this problem. It appears you are maybe importing music library data, which is exactly what I was doing. I use MP3Tag to export to CSV and then did an SSIS package for the import to SQL Server, and constantly got truncation errors reported.
Don't know if this will help, but what I determined was that many of my music files had tags that contained certain characters embedded in them that were causing problems for the import. The only solution I found was to painstakingly review the tag data and re-edit to remove such characters before the import. This took some time as I have nearly 70,000 music files, but now I do have my library cleaned at the source and the truncation error has gone away, except for when I add new tracks.
Also, if this is what you are doing, NEVER allow your application to download tag data from internet sources. That data is extremely poor quality with almost no standards,
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
August 9, 2021 at 3:55 pm
Good - but do remember that in order to use a tool you need to learn how to use it - if you are like this with a utility like Data Import which has a small number of features that you didn't bother reading about or even play with then your travel on the world of SQL Server is bound to be disastrous - hopefully your clients will see it before your actions break their systems.
Hey, 'Come On, Man'. Give him a break. I have 42 years of IT work behind me and I still fought the same issues with imports. I'm 78 years old and still learning. I applaud that he is working through the problems and seeking guidance. I don't think I even began to use SSIS and such until I was in my 60's, and I'm still trying to master the nuances of it.
We don't always have the luxury of "reading about or even play with" all the features until we need to use them. And besides, the error information from features is often not as clear as it could be.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
August 9, 2021 at 4:03 pm
@vscurtis...
Still waiting to hear which version of SQL Server you're using.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 9, 2021 at 4:46 pm
Good - but do remember that in order to use a tool you need to learn how to use it - if you are like this with a utility like Data Import which has a small number of features that you didn't bother reading about or even play with then your travel on the world of SQL Server is bound to be disastrous - hopefully your clients will see it before your actions break their systems.
[/quote]
VS, I just had another thought that might help you with the import. When I was having the repeated truncation error, I found that I could load the files into NotePad++ first, which I recall will let you search for characters within or without a range for a character set, and that let me find the characters that were distorting my data. you might try that with your data and see if it helps.
Good luck.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
August 9, 2021 at 4:55 pm
frederico_fonseca wrote:Good - but do remember that in order to use a tool you need to learn how to use it - if you are like this with a utility like Data Import which has a small number of features that you didn't bother reading about or even play with then your travel on the world of SQL Server is bound to be disastrous - hopefully your clients will see it before your actions break their systems.
Frederico, I just had another thought that might help you with the import. When I was having the repeated truncation error, I found that I could load the files into NotePad++ first, which I recall will let you search for characters within or without a range for a character set, and that let me find the characters that were distorting my data. you might try that with your data and see if it helps.
Good luck.[/quote]
hi,
It not me having the issue, but someone else that has likely left the building.
also do note that as far as a valid CSV files goes there is no such thing as a invalid character - there may be characters on incorrect code pages, or the file may not have been created in the correct code page in the first place, but that is altogether a different issue with different solutions.
Issue here was that someone tried to use a tool without setting it up correctly and that gave some errors (which we do not know what they are as we were never informed of the detail, although from experience it was related to the size of the columns not being defined and left as their defaults).
Thanks anyway for thinking of some possible issues.
August 9, 2021 at 5:21 pm
skeleton567 wrote:frederico_fonseca wrote:Good - but do remember that in order to use a tool you need to learn how to use it - if you are like this with a utility like Data Import which has a small number of features that you didn't bother reading about or even play with then your travel on the world of SQL Server is bound to be disastrous - hopefully your clients will see it before your actions break their systems.
Frederico, I just had another thought that might help you with the import. When I was having the repeated truncation error, I found that I could load the files into NotePad++ first, which I recall will let you search for characters within or without a range for a character set, and that let me find the characters that were distorting my data. you might try that with your data and see if it helps.
Good luck.
hi,
It not me having the issue, but someone else that has likely left the building.
also do note that as far as a valid CSV files goes there is no such thing as a invalid character - there may be characters on incorrect code pages, or the file may not have been created in the correct code page in the first place, but that is altogether a different issue with different solutions.
Issue here was that someone tried to use a tool without setting it up correctly and that gave some errors (which we do not know what they are as we were never informed of the detail, although from experience it was related to the size of the columns not being defined and left as their defaults).
Thanks anyway for thinking of some possible issues.[/quote]
Yeah, got the reply to the wrong comment. Sorry about that.
As far as the invalid characters in CSV files, that may be true, but when it hit my SQL Server import there were in fact 'characters' (byte values) that distorted the way the SSIS package interpreted the CSV file. Also remember that there are 'characters' which are not visible but which can do strange things to the import. That is what I found, and when I cleaned them, then SSIS magically decided that it liked my data. Sometimes we need to use a different tool to help us learn the tool at hand. The trick is finding the right tool to fix the tool.
Please don't beat this poor guy up for asking for help. Might be simple for some of us, but I'm sure he is frustrated and was calling out for help. I'm sort of disappointed in some of the responses to his query.
Rick
Disaster Recovery = Backup ( Backup ( Your Backup ) )
Viewing 8 posts - 31 through 37 (of 37 total)
You must be logged in to reply to this topic. Login to reply