import *.dat files into sql server tables
I have many *.dat files. I want to insert selected columns from each file into database. How can I automate this process using c#,.net ,bcp utility and format files. Or does sql server have the ability to automate this process.
Also as you can see in the attachment it has some columns but it also has some columns after a lot of space which i could not get in the screenshot. Please help
also i just want the some of the columns like i dont want any value until Mname. I want values from the next column ex for last row i want
2775 in 1 column then 191 in 1 column then 4 in 1 column then 3 in 1 column and so on.
Just to elaborate 191 is test number and "4 " "3 " are responses. Thank you.
Also as you can see in the attachment it has some columns but it also has some columns after a lot of space which i could not get in the screenshot. Please help
also i just want the some of the columns like i dont want any value until Mname. I want values from the next column ex for last row i want2775 in 1 column then 191 in 1 column then 4 in 1 column then 3 in 1 column and so on.
Just to elaborate 191 is test number and "4 " "3 " are responses. Thank you.
ASKER
Thanks for the answer. its almost what i wanted but I dont know how to implement it using c#. I have already read the documents about the bcp utility, bulk insert and format file . But i dont know how to start. If you can only just get me started i can then develop the remaining logic. Actually I have problem implementing bcp utility via c#. I dont know the syntax to run cmd functions using c#. Thats where I am stuck. This is new for me. I am not an expert but I have some experience in asp.net and c#. But getting started here is the concern. Because we have to automate this process and have to develop a full application. Sorry for long text. Thanks
First of all have you got BCP to import the data correctly. Unless you have bridged that gap there is no point in discussing how you are going to execute that using C#
It seems that you are having issues using BCP import.
Another possible solution is to use SqlBulkCopy class as shown here.
Basically you load your CSV file (to load tab file just change the delimeter (e.g for tab it should be csvReader.SetDelimiters(ne
Another possible solution is to use SqlBulkCopy class as shown here.
Basically you load your CSV file (to load tab file just change the delimeter (e.g for tab it should be csvReader.SetDelimiters(ne
ASKER
Thanks for this.This might work at some extent but my requirement is getting some of the columns only and sqlbulkcopy cant do this. It just copies all data and paste it to sql table which will not complete the data trasnfer and give us redundant records.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I have encountered another issue with the importing. 
As you can see the last column
41113411411322414212422443
Column1 column2 column3 column4
4 1 1 1
is this possible?. I can use both bcp with format file and sqlbulky copy or bulkimport for dat file. My limitation is i have to use .dat file or change the extension of that dat file to txt. no other format.
As you can see the last column 41113411411322414212422443
Column1 column2 column3 column4
4 1 1 1
is this possible?. I can use both bcp with format file and sqlbulky copy or bulkimport for dat file. My limitation is i have to use .dat file or change the extension of that dat file to txt. no other format.
Please clarify, it seems to me that you need the import the last column to a different table. Is this correct?
If so, how do you define the delimiter(last column) for the second table?
Is there any relationship between these tables?
As you can see the problem may seem bigger that the scope of his question, my suggestion will be to elaborate a new related question with the above questions answered.
If so, how do you define the delimiter(last column) for the second table?
Is there any relationship between these tables?
As you can see the problem may seem bigger that the scope of his question, my suggestion will be to elaborate a new related question with the above questions answered.
ASKER
No. I want to import the last column in the same table but not as whole. As you can see the last column
41113411411322414212422443
Column1 column2 column3 column4
4 1 1 1
Yes . The problem is of defining delimiter as it doesn't have any delimiter. I would like to say that i don't want to import data in the in different table. The data needs to be imported in same table but with splitting of columns
41113411411322414212422443
Column1 column2 column3 column4
4 1 1 1
Yes . The problem is of defining delimiter as it doesn't have any delimiter. I would like to say that i don't want to import data in the in different table. The data needs to be imported in same table but with splitting of columns
the format file contains the required column information including how to delimit the columns. (Your file looks like a tabbed text so delimiter is tab and the final column delimiter is new line)
The complete solution will depend on your business process (how the data files are supplied/stored). For example if they are supplied via asp.net and stored in a server location accessible to SQL server then you could run from C# a store procedure (that contains your bulk insert statement and the logic to map your table)