How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better wway to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

May I suggest posting this question on sql server programming news group?

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming

Thanks

|||

You might get extra help in sql server programming news group for these kind of questions.

thanks

http://www.microsoft.com/technet/community/newsgroups/dgbrowser/en-us/default.mspx?dg=microsoft.public.sqlserver.programming

|||Thanks. I'll do that.

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

Take a look at this post. I have given an example using OleDbCommand and SqlCommand.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Note that I am reading from excel and inserting into SQL database.

Hope this helps

|||

Sample code below:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||

Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.

Ed

"I don't know what I don't know."

How do I insert data from a flat file or .csv file into an existing SQL database?

How do I insert data from a flat file or .csv file into an existing SQL database?

Here what I've come up with thus far and I but it doesn't work. Can someone please help? Let me know if there is a better way to do this... Idealy I'd like to write straight to the sql database and skip the datset all together...

strSvr = "vkrerftg"

StrDb = "Test_DB"

'connection String

strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated security=SSPI;"

Dim dbconn As New SqlConnection(strCon)

Dim da As New SqlDataAdapter()

Dim insertComm As New SqlCommand("INSERT INTO [Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company Loss], " & _

"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _

"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC, @.eventsInfo)", dbconn)

insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.InsertCommand = insertComm

Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _

"SET [Event] = @.Event " & _

",[Year] = @.Year " & _

",[Contract Loss] = @.ConLoss " & _

",[Company Loss] = @.CompLoss " & _

",[IndInsured Loss Prop] = @.IndLossProp " & _

",[IndInsured Loss WC] = @.IndLossWC " & _

",[Event Info] = @.EventInfo", dbconn)

upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")

upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")

upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")

upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")

upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss Prop")

upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC")

upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")

da.UpdateCommand = upComm

da.Update(dsAIR, "TextDB")

************* ANY HELP WOULD BE GREATLY APPRECIATED************

THANKS

Take a look at this post. I have given an example using OleDbCommand and SqlCommand.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=320478&SiteID=1

Note that I am reading from excel and inserting into SQL database.

Hope this helps

|||

Sample code below:

Dim cn As New OleDbConnection
Dim sqlcn As New SqlConnection
Dim adapter As New OleDbDataAdapter
Dim dtset As New DataSet
Dim dt As New DataTable
Dim cmd As New OleDbCommand
Dim sqlcmd As New SqlCommand
Dim dr As DataRow

cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= C:\;" + "Extended Properties=""Text;HDR=Yes;"""
sqlcn.ConnectionString = "Data Source=.;Integrated Security=SSPI;"

cn.Open()
sqlcn.Open()

cmd.Connection = cn
cmd.CommandText = "Select * from test.csv"
adapter.SelectCommand = cmd
adapter.Fill(dtset, "MyTable")
dt = dtset.Tables("MyTable")

sqlcmd.Connection = sqlcn
sqlcmd.CommandText = "Insert into MyParts (PartNumber,PartName) Values(@.a,@.b)"
sqlcmd.CommandType = CommandType.Text

sqlcmd.Parameters.Add("@.a", SqlDbType.VarChar, 20)
sqlcmd.Parameters.Add("@.b", SqlDbType.VarChar, 20)

For Each dr In dt.Rows
sqlcmd.Parameters("@.a").Value = dr(0).ToString()
sqlcmd.Parameters("@.b").Value = dr(1).ToString()
sqlcmd.ExecuteNonQuery()
Next

cn.Close()
sqlcn.Close()

Hope this helps

|||Raj you are a life saver... with a few minor conversions that was exactly what I needed... Thanks Again|||

Too much work... Look up Linkserver/Excel under help. After this you can query the excel file as a table and if you like you can use normal sql to update your other table from the excel file which is now linked to your server.

Ed

"I don't know what I don't know."

How do I insert data from a flat file into an existing SQL databas

How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all together.
.
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss WC"
)
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKSYou can use the command line utility - bcp.exe to load from a flat file.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Bermychild" <Bermychild@.discussions.microsoft.com> wrote in message
news:7BDDC48D-4D33-42E5-B69B-11976FE75CC4@.microsoft.com...
How do I insert data from a flat file or .csv file into an existing SQL
database?
Here what I've come up with thus far and I but it doesn't work. Can someone
please help? Let me know if there is a better way to do this... Idealy I'd
like to write straight to the sql database and skip the datset all
together...
strSvr = "vkrerftg"
StrDb = "Test_DB"
'connection String
strCon = "Server=" & strSvr & ";database=" & StrDb & "; integrated
security=SSPI;"
Dim dbconn As New SqlConnection(strCon)
Dim da As New SqlDataAdapter()
Dim insertComm As New SqlCommand("INSERT INTO
[Test_DB_RMS].[dbo].[AIR_Ouput] ([Event], [Year], [Contract Loss],[Company
Loss], " & _
"[IndInsured Loss Prop],[IndInsured Loss WC],[Event Info]) " & _
"VALUES (@.Event, @.Year, @.ConLoss, @.CompLoss, @.IndLossProp, @.IndLossWC,
@.eventsInfo)", dbconn)
insertComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
insertComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
insertComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
insertComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
insertComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured
Loss Prop")
insertComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
insertComm.Parameters.Add("@.eventsInfo", SqlDbType.NVarChar, 255, "Event
Info")
da.InsertCommand = insertComm
Dim upComm As New SqlCommand("UPDATE [Test_DB_RMS].[dbo].[AIR_Ouput] " & _
"SET [Event] = @.Event " & _
",[Year] = @.Year " & _
",[Contract Loss] = @.ConLoss " & _
",[Company Loss] = @.CompLoss " & _
",[IndInsured Loss Prop] = @.IndLossProp " & _
",[IndInsured Loss WC] = @.IndLossWC " & _
",[Event Info] = @.EventInfo", dbconn)
upComm.Parameters.Add("@.Event", SqlDbType.Int, 4, "Event")
upComm.Parameters.Add("@.Year", SqlDbType.Float, 4, "Year")
upComm.Parameters.Add("@.ConLoss", SqlDbType.Float, 4, "Contract Loss")
upComm.Parameters.Add("@.CompLoss", SqlDbType.Float, 4, "Company Loss")
upComm.Parameters.Add("@.IndLossProp", SqlDbType.Float, 4, "IndInsured Loss
Prop")
upComm.Parameters.Add("@.IndLossWC", SqlDbType.Float, 4, "IndInsured Loss
WC")
upComm.Parameters.Add("@.EventsInfo", SqlDbType.NVarChar, 255, "Event Info")
da.UpdateCommand = upComm
da.Update(dsAIR, "TextDB")
************* ANY HELP WOULD BE GREATLY APPRECIATED************
THANKS|||Try to use BCP or BULK INSERT.
BCP can be done from the command line
BULK INSERT would be done from inside Query Analyzer.
For example: Store the following in a .bat file and run...
----
--
bcp [database].[owner].[tablename]
IN "D:\DataFilePath\DataFileName.ext"
-f D:\FormatFilePath\FormatFileName.fmt
-o D:\OutputFilePath\OutputFileName.fmt
-m 10000 -F 1 -c -S [ServerName] -T
----
--
Since this will be the first time you are runnning, remove the line
with
" -f D:\FormatFilePath\FormatFileName.fmt"
The bcp utility will prompt you when u run it and create the Format
File automatically.
The BULK INSERT Format is easier
BULK INSERT
database_name.schema_name.table_name
FROM 'data_file'
WITH
(
, FIELDTERMINATOR = ','
, FIRSTROW = 1
, KEEPNULLS
, MAXERRORS = 10000
, ROWTERMINATOR = '\n'
, TABLOCK
, ERRORFILE = 'file_name'
)
Also, if the flat file data is not in the same format as the
destination table, I recommend creating a TEMP TABLE...
CREATE TABLE ##bulkinsert() to match the specification of the datafile,
BULK INSERT or BCP into that file, and then use a normal SQL Query to
transport data from the temp table to the final table. This also allows
you the opportunity to clean the data in the temp table by removing
quotation marks, and filtering out invalid data prior to moving data to
your production table.s|||If you're using .Net 2.0, you can look at my component I sell,
http://www.csvreader.com , that gives syntax like below that I took off
my code samples page. It basically inserts at DTS speed and doesn't
have some of the issues as BCP and BULK INSERT.
Using loader As CsvDataReader = New CsvDataReader("somefile.csv")
loader.HasHeaders = True
loader.Columns.Add("varchar") ' First
loader.Columns.Add("varchar") ' Last
loader.Columns.Add("datetime") ' First Sale
loader.Columns.Add("money") ' Amount
Using bulkCopy As SqlBulkCopy = New SqlBulkCopy("Data
Source=myServer;Initial Catalog=Test;User ID=sa;Password=")
bulkCopy.DestinationTableName = "Customer"
bulkCopy.WriteToServer(loader)
End Using
End Using
Bruce Dunwiddie
dizzler wrote:
> Try to use BCP or BULK INSERT.
> BCP can be done from the command line
> BULK INSERT would be done from inside Query Analyzer.
> For example: Store the following in a .bat file and run...
> ----
--
> bcp [database].[owner].[tablename]
> IN "D:\DataFilePath\DataFileName.ext"
> -f D:\FormatFilePath\FormatFileName.fmt
> -o D:\OutputFilePath\OutputFileName.fmt
> -m 10000 -F 1 -c -S [ServerName] -T
> ----
--
> Since this will be the first time you are runnning, remove the line
> with
> " -f D:\FormatFilePath\FormatFileName.fmt"
> The bcp utility will prompt you when u run it and create the Format
> File automatically.
>
> The BULK INSERT Format is easier
>
> BULK INSERT
> database_name.schema_name.table_name
> FROM 'data_file'
> WITH
> (
> , FIELDTERMINATOR = ','
> , FIRSTROW = 1
> , KEEPNULLS
> , MAXERRORS = 10000
> , ROWTERMINATOR = '\n'
> , TABLOCK
> , ERRORFILE = 'file_name'
> )
>
> Also, if the flat file data is not in the same format as the
> destination table, I recommend creating a TEMP TABLE...
> CREATE TABLE ##bulkinsert() to match the specification of the datafile,
> BULK INSERT or BCP into that file, and then use a normal SQL Query to
> transport data from the temp table to the final table. This also allows
> you the opportunity to clean the data in the temp table by removing
> quotation marks, and filtering out invalid data prior to moving data to
> your production table.ssql

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

How do i Edit the Package Source file name Dynamically

Hi

I have created a package using DTS Designer to import the data from flat file to the SQL server.
i have sheduled the package to execute at every 1st day of the month and saved the the package to the local server.

i want to do the following steps dynamically from the Visual Basic

1. i am loading all the packages from the local server and also from the metadata services into a combo box. select one package from the combo box and i want to change the package shedule time from vb and i want to save the package back to the sql server.

2. when the package is executing at the schedule time at that time i want to change the source of the package that means flat filename(to different filename) and i need to execute the package and save the package.

how do i do this dynamically.

Thanks in advance

Regards
GandhiThe package schedule time is really a job schedule time - so you would use the sp_update_jobschedule stored procedure. For the 2nd part, when would the filename change and what would change it ?