Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  Development Tools  ASP.NET  Import CSV to SQL table problem

Author  Topic  SNandra Starting Member 4 Posts Posted - 2008-02-28 : 11:39:01 Hi written some asp.net vb.net code that pick a csv from a drive and then uploads it to the webserver. - which works fine. Then I need to read that csv and upload it to a SQL database table. The problem is when I insert the CSV to my SQL table. All Table rows are displaying the first column data. It is not splitting my comma seperated CSV. Any ideas how to ammend my fuunction? Public Function convertDatabase() Dim dr1 As SqlDataReader Dim MyPath, FileExists As String MyPath = "\\Server\ETest\Reports\" + c FileExists = Dir(MyPath, vbDirectory) If FileExists = "" Then Span2.InnerHtml = "CSV file has not been uploaded!" Else cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data source= \\Server\ETest\Reports\;" + "Extended Properties=""Text;HDR=YES;IMEX=1;FMT=Delimited(;)""" sqlcn.ConnectionString = "server=epapp6;Initial Catalog=DB;user id=ME;password=pwd2;" cn.Open() sqlcn.Open() cmd.Connection = cn cmd.CommandText = "Select [Client Weekend Date],[Weekend Date],[Candidate],[Timesheet ID],[Candidate ID],[monday],[tuesday],[wednesday],[thursday],[friday],[saturday],[sunday],[Total Hours],[STD Hours] FRoM " & c adapter.SelectCommand = cmd adapter.Fill(dtset, "Hours") dt = dtset.Tables("Hours") sqlcmd.Connection = sqlcn sqlcmd.CommandText = "Insert into " & DataTable(0) & ".dbo.WeeklyTS1 (ClientWeekendDate, WeekendDate,Candidate,TimesheetID,CandidateID,Mon,Tues,Wed,Thurs,Fri,Sat,Sun,TotalHours,STDHours) " & _ " Values(@ClientWeekendDate, @WeekendDate,@Candidate,@TimesheetID,@CandidateID,@Mon,@Tues,@Wed,@Thurs,@Fri,@Sat,@Sun,@TotalHours,@STDHours)" sqlcmd.CommandType = CommandType.Text sqlcmd.Parameters.Add("@ClientWeekendDate", SqlDbType.VarChar, 20) sqlcmd.Parameters.Add("@WeekendDate", SqlDbType.VarChar, 20) sqlcmd.Parameters.Add("@Candidate", SqlDbType.VarChar, 50) sqlcmd.Parameters.Add("@TimesheetID", SqlDbType.VarChar, 13) sqlcmd.Parameters.Add("@CandidateID", SqlDbType.VarChar, 13) sqlcmd.Parameters.Add("@Mon", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@Tues", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@Wed", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@Thurs", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@Fri", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@Sat", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@Sun", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@TotalHours", SqlDbType.VarChar, 8) sqlcmd.Parameters.Add("@STDHours", SqlDbType.VarChar, 8) For Each dr In dt.Rows sqlcmd.Parameters("@ClientWeekendDate").Value = dr(0).ToString() sqlcmd.Parameters("@WeekendDate").Value = dr(0).ToString() sqlcmd.Parameters("@Candidate").Value = dr(0).ToString() sqlcmd.Parameters("@TimesheetID").Value = dr(0).ToString() sqlcmd.Parameters("@CandidateID").Value = dr(0).ToString() sqlcmd.Parameters("@Mon").Value = dr(0).ToString() sqlcmd.Parameters("@Tues").Value = dr(0).ToString() sqlcmd.Parameters("@Wed").Value = dr(0).ToString() sqlcmd.Parameters("@Thurs").Value = dr(0).ToString() sqlcmd.Parameters("@Fri").Value = dr(0).ToString() sqlcmd.Parameters("@Sat").Value = dr(0).ToString() sqlcmd.Parameters("@Sun").Value = dr(0).ToString() sqlcmd.Parameters("@TotalHours").Value = dr(0).ToString() sqlcmd.Parameters("@STDHours").Value = dr(0).ToString() sqlcmd.ExecuteNonQuery() Next cn.Close() sqlcn.Close() sqlcmd.CommandText = "SELECT COUNT(*) AS rows FrOM " & DataTable(0) & ".dbo.WeeklyTS1" sqlcn.Open() sqlcmd.CommandType = CommandType.Text Dim rowCount As String result = sqlcmd.ExecuteReader() result.Read() rowCount = result.GetValue(0) Span2.InnerHtml = rowCount & " csv rows converted to SQL table " End If End Function