| Author |
Topic |
|
voyager838
Yak Posting Veteran
90 Posts |
 Posted - 2011-08-19 : 10:57:43
|
| Hi im facing a problem with importing csv-data with bcp in command-line.How to import csv-file, that has fieldnames in first row but not fieldtypesinformation.Do i always have to create an new table preparing for each csv-fil i import for that or specify a fmt file (alt xml) or can it be imported directly to let say a new table created on-the-fly with for instance varchar type, so i can edit that laterly in the database instead of doing knowing everything before i import data?Its really a painful situation to import data in sql-server, has anyone notice that ?, some good idea how to would be really helpful.Thanks |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-19 : 12:07:27
|
| it can exported to a new table on the fly using sql export import wizard. the table will be created based on datatypes of data which is present in csv.one question is will your csv keep on changing each time?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2011-08-19 : 12:35:41
|
quote:
Originally posted by visakh16
it can exported to a new table on the fly using sql export import wizard. the table will be created based on datatypes of data which is present in csv.one question is will your csv keep on changing each time?
Not regular but sometimes it changes, so next time it could have several fields that has to be created to the same table. I want to import a plan datafile (csv) without having to worry or knowing anything about the field och those filename, but i dont' want to do it manually because this moment is repeating for several file each day, so it has to be done automatically if possible. One solution could be (speculating) doing some kind of a simple .NET app that ticks every hour and let the apps handle the importing data if its exist. That would be possibleBut if it could be avoided that would be great. It consume to much time to developing.thank |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-19 : 12:48:32
|
| if your metadata changes at runtime its not possible to vary the table structure accordingly using any of standard methods. why is it that structure cant be generalised ?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2011-08-19 : 13:03:36
|
quote:
Originally posted by visakh16
if your metadata changes at runtime its not possible to vary the table structure accordingly using any of standard methods. why is it that structure cant be generalised ?
Data is being created in realtime into files every hour.First it could be handled by manually, but when the mainstream of data increase the files being created with varied number of fields. I know one thing the field in every file is always (except the datetime field) in float. That is a good sign. right?Its looks like (i though) solution was simple, to create some kind of a simple manage batch-file that includes bcp or sqlcmd to import csv files, when command. in one single click.But the mainproblem still remain, the number of fields vary. The other solution is to develop some kind of a simple application in .NET i had hope to avoided that.  Maby one solution also could be to create the simple applicaton that builds the .fmt file and the sql-file to simplify it up a bit. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-08-19 : 13:18:43
|
| ok let's say the vary but is there a max set of fields? meaning there can be field1field2field3field4field5 as max amount of fields but soemtimes it isfield1field2and sometimes it is field1field2field3or is it that sometimes there are gaps?make all fields NULLIf you don't have the passion to help people, you have no passion |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2011-08-19 : 13:27:48
|
quote:
Originally posted by yosiasz
ok let's say the vary but is there a max set of fields? meaning there can be field1field2field3field4field5 as max amount of fields but soemtimes it isfield1field2and sometimes it is field1field2field3or is it that sometimes there are gaps?make all fields NULLIf you don't have the passion to help people, you have no passion
It can vary, and lucky there is (maby) it only increase in number of fields. It never has decreased the field somewhere as i can see and all the old fields has the same name as before except the new fields ofcourse and it is always being stored with float-value. |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2011-08-19 : 14:01:33
|
| Maby one solution for this kind of problems would be:Firstly create a procedure in database that takes a string of fieldname (separated by semicolon or another delimiter) in the parameter andlet the procedure use that string to see if the fields exists in some table, or if has been changed and than add a column with float-type in database if needed.This procedure separate the fields and for each fieldname look what to do with it, compare and change anything and so on.Next is to setup a somekind of an importing-tool, maby is being created in .NET (or VB or C++-application what-ever suits the need but can handle files and strings) that reads the csv-file, we only reading the the first row (fieldnames) and creating an sql-file based on the fieldname that continue a call to the procedure and use well prepared BULK insert command or OPENROWSET, so next time i only go to my batch-file (using linux, .bat for ms-users),creating an .bat file continue a sqlcmd that calling the sql-file, and hopefully everything works smoothly.This is basic steps, it needs more... lots of more codes :)It could be benefit for several situations if the data is being vary or has different types when importing some huge data.Note: we could indeed develop fully a stand-alone importing tool in our language but this solutions would work if you don't have plan to create a hole new export-import-tool (in that case it could be bought anyway) and more want the your OS and SQL Server handle it without trouble with some new applications, and this suits the needs very well if you prepare the procedure carefully. All depends on the procedure how advanced you want it to be. The import-tool only simple reads an single (or several) file, and create another one.As we can see, it would looks like a elegant solutions if you are a developer, but i had hope to avoid it a bit.Hope the solutions would be useful for others in similar situations.First i though this has been done before by MS. But i guess it hasn't yet. |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-08-19 : 14:10:41
|
| keep it simple. in my opinion elegant solution is always simple!! So let's go back to basics1. What is the destination of the data in csv?2. What is it consumed by3. What generates the csv?please answer theseIf you don't have the passion to help people, you have no passion |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2011-08-19 : 14:23:32
|
| @Youslasz : You are absolutely right. It should be to keep it simple as possible, i totally agree!1. The destination of the data is to the table already once created in the database2. The consuming is to manually import each file into the database.Every csv-file changes its number of fields not regular.This mean maby it has to been automatically a bit. As you pointed it out, keep it simple. This files could be big (i forget to mention a new file is being created sometimes)and filename 3. csv-file came from different sources. It is only imported into a filemap on the HD. So the source that create the csv-file can't be reach or changed.the best thing would had been if every file was created with plan-data based on rows not on columns,based on ID | ComponentName | DateTime | Valuebut now its not the case,i can't do something to about it.Thanks |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-08-19 : 14:30:40
|
| are these external customers or internal? might you acquire new customers or csv sources? make your destination table have the max number of columns in csv. do you have any idea what is the max column count? make all fields nullIf you don't have the passion to help people, you have no passion |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2011-08-19 : 16:11:23
|
quote:
Originally posted by yosiasz
are these external customers or internal? might you acquire new customers or csv sources? make your destination table have the max number of columns in csv. do you have any idea what is the max column count? make all fields null
Little of both for the moment, yes i expect new external customers would came with more data. The csv-files hasn't any max-limit which worry me a bit, if it grows rapidly. No idea of what it could be. Thats why i guess this solutions could help me avoid to handle that. Please explain a little further what you suggest with "make all fields null". Curious.Thanks/Voy |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2011-08-19 : 16:18:45
|
| I am talking about max limit of columns not the size of it, what is max number of columns you will get. I do not think you can automate your thing unless you dynamically create a new temp table every time you get a new csv. by null I meant to say make them nullable. so that in case column values are not sent it stays null.If you don't have the passion to help people, you have no passion |
|
|
|
voyager838
Yak Posting Veteran
90 Posts |
Posted - 2011-08-19 : 17:36:40
|
| Yes i talk about the limit as number of columns, i wasn't clear to explain it, sorry about that. There is none max-limits except what sql-server can provide that will be 500 ? (not sure) I don't know how big this really is going to be, but it seems to me that it stays around 40-300 columns, so that must be the case i can guess. mostly its around 100.However it seems that in my opinion that this will have to be an dynamic solutions somehow.So yes u-r right i cannot automate it unless its dynamically created and handled. I see that my solutions could provide that now.I realize an important thing, that in this case the limitation with a feew columns and saves the new components incoming in row than in columns, would rather had been better in the first place.But some wise told me once "Failing to plan is Planning to fail" (i wonder who that could be ;) )Thanks for answering and helping i really appreciate it! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-20 : 02:04:23
|
| there's one way in which you can do this. this is by using ssis having a for each loop to loop through the files and then using a script task inside to initialise a data flow task with metadata from file and then transfer it to tables. Based on the column details you can redirect it to one of destination tables.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
Import csv-data