Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3674
  • Last Modified:

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 helpsample dat file contentsalso 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.
0
SQL .NET
Asked:
SQL .NET
  • 4
  • 4
1 Solution
 
Miguel OzSoftware EngineerCommented:
A possible SQL server solution Use a Format File to Bulk Import Data
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)
0
 
SQL .NETAuthor Commented:
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
0
 
Anthony PerkinsCommented:
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#
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
Miguel OzSoftware EngineerCommented:
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(new string[] { "\t" })) into a data table ( and then you copy this table to SQL server.
0
 
SQL .NETAuthor Commented:
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.
0
 
Miguel OzSoftware EngineerCommented:
Use SqlBulkCopyColumnMapping to map which columns will be copied.
If you do not create a mapping for a column, it will be ignored by the copy process.
0
 
SQL .NETAuthor Commented:
I have encountered another issue with the importing. s.jpg As you can see the last column
411134114113224142124224434. I have to import this as a separate column like

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.
0
 
Miguel OzSoftware EngineerCommented:
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.
0
 
SQL .NETAuthor Commented:
No. I want to import the last column in the same table but not as whole. As you can see the last column
411134114113224142124224434. I have to import the every single number in separate column.
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
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now