• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 141
  • Last Modified:

How to create a SQL Server Table using CSV text file

How can i create a table using CSV text file specification
I have a CSV file a I want to use it to create a table in SQL Server. The file has variable number of columns in each line (example: 1'st line = 12 columns, 12'th line = 14 columns).
  • 2
1 Solution
Are you interested in creatng a general purpose program to be used in an unattended mode on a repetitive basis, or is this just a one time special conversion?  

Anyway, if you ship me a small part of the front of your file, I'll take a look and see if I can whip out a small routine from the megs of VB/Basic code under my belt here at cymbolic@aol.com.

Just attach it to a note with enough specific information so I can get the job done.
I think you could use the Distributed Managment Objects(SQL-DMO) that should be on the SQL Server CD to create these.  I know a guy who wrote code that creates tables and stored procs based on class design.  He used SQL-DMO and found it to be pretty easy and useful.

You can use transact SQL and the Create Table syntax in the .execute method of RDO to create a table.  You can also use SQL-DMO by including the SQLOLE object in your project.  It also supports the use of Transact SQL and provides database and table objects and collections, if you're into that metaphor.  A complete SQL-DMO Object model can be downloaded from the MSDN site on the Web.

Here's a copy of an SQL Script that creates a table:

/****** Object:  Table dbo.PchFilLst    Script Date: 7/9/98 3:09:22 PM ******/
if exists (select * from sysobjects where id = object_id('dbo.PchFilLst') and sysstat & 0xf = 3)
      drop table dbo.PchFilLst

/****** Object:  Table dbo.PchFilLst    Script Date: 7/9/98 3:09:22 PM ******/
CREATE TABLE dbo.PchFilLst (
      PchNum char (15) NOT NULL ,
      FilNum int NOT NULL ,
      FilNam char (25) NULL ,
      FilTyp char (1) NULL ,
      BldVer char (15) NULL ,
      LodMsk char (15) NULL ,
      Lap char (1) NULL ,
      UsrAdd char (1) NULL ,
      WrkStn char (1) NULL ,
      Reg char (1) NULL ,
      LapFilLoc char (255) NULL ,
      SrvFilLoc char (255) NULL ,

The variable number of columns is another problem, but you can solve that yourself by reading the file in VB, and creating your own Insert Into <table> SQL for each row and doing the inserts yourself using the .execute method.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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