|
|
You're in the
sql server programming
group:Date in csv file are NULL!!!
sql server programming:
Hello group
I need Assistance on this one. I have a VB6 application which imports csv data in a database. There are 24 csv files. Some files contain dates like this example ENL80071666;REG00024;1999-01-01;1999-06-03;1999-12-31 ENL80071666;REG00024;2000-01-01;2000-01-01;2000-12-31 ENL80071666;REG00024;2001-01-01;2001-01-01;2001-12-31 ENL80071666;REG00024;2002-01-01;2002-01-01;2002-12-31 ENL80071666;REG00024;2003-01-01;2003-01-01;2003-03-31 They are imported in temporary tables with the help of this SP. set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER PROCEDURE [dbo].[I_TDE_Imports] @FilePath VARCHAR(500), @FileName VARCHAR(40), @TableName VARCHAR(6) AS DECLARE @sql NVARCHAR(2000) DECLARE @Delete NVARCHAR(40) SET @Delete = 'TRUNCATE TABLE ' + @TableName EXECUTE sp_executesql @Delete SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName + ''')' EXECUTE sp_executesql @sql This is an exemple of my schema.ini [TDE003.csv] ColNameHeader=False Format=Delimited(;) MaxScanRows=0 CharacterSet=ANSI COL1=no_empl char width 12 COL2=no_rrr char width 12 COL3=datda_rrr_perio date width 10 COL4=datad_empl_rrr date width 10 COL5=dat_ret_empl_rrr date width 10 Thereafter, there are several SP which are occupied of placing this information in the working tables. On my laptop, all functions correctly. I have two clients. One of them has many problems to import files which contain dates. Sometime it works, sometime it don't. For a reason that I am unaware of, all the date fields that are link to a date in the csv file are Null in the temporary tables. However, his laptop has the same configurations as mine. I don't understands.... Why this difference? and most of all, where to look for a solution??????? I need help Thank you very much Marc R.
Are you talking to me?
[quoted text, click to view] "kishor" <kishor@discussions.microsoft.com> wrote in message news:9655E1CD-C888-492F-964B-D84810173B00@microsoft.com... > Hey ... > I have not gone through your mail .. but just giving you one hint .. may > be > it works. > > please check the datetime format of sql server and your machine. it could > be > different. > > Regards, > kishor > > > > "Uri Dimant" wrote: > >> Mark >> I did some testing and it wokrd just fine. See on schema.ini file I have >> changed for datetime columns >> EXEC sp_addlinkedserver txtfile, 'Jet 4.0', >> 'Microsoft.Jet.OLEDB.4.0', >> 'c:\', >> NULL, >> 'Text' >> select * >> into #temp >> from txtfile...TDE003#csv >> >> >> select * from #temp >> go >> drop table #temp >> >> [TDE003.csv] >> ColNameHeader=False >> Format=Delimited(;) >> DateTimeFormat=yyyy-mm-dd >> MaxScanRows=0 >> CharacterSet=ANSI >> COL1=no_empl Text >> COL2=no_rrr Text >> COL3=datda_rrr_perio DateTime >> COL4=datad_empl_rrr DateTime >> COL5=dat_ret_empl_rrr DateTime >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Marc Robitaille" <marc.marie AT globetrotter.net.del> wrote in message >> news:ud6AdeS6HHA.3740@TK2MSFTNGP02.phx.gbl... >> > Hello group >> > >> > I need Assistance on this one. I have a VB6 application which imports >> > csv >> > data in a database. There are 24 csv files. Some files contain dates >> > like >> > this example >> > >> > ENL80071666;REG00024;1999-01-01;1999-06-03;1999-12-31 >> > ENL80071666;REG00024;2000-01-01;2000-01-01;2000-12-31 >> > ENL80071666;REG00024;2001-01-01;2001-01-01;2001-12-31 >> > ENL80071666;REG00024;2002-01-01;2002-01-01;2002-12-31 >> > ENL80071666;REG00024;2003-01-01;2003-01-01;2003-03-31 >> > >> > They are imported in temporary tables with the help of this SP. >> > >> > set ANSI_NULLS ON >> > set QUOTED_IDENTIFIER ON >> > go >> > >> > ALTER PROCEDURE [dbo].[I_TDE_Imports] >> > @FilePath VARCHAR(500), >> > @FileName VARCHAR(40), >> > @TableName VARCHAR(6) >> > AS >> > >> > DECLARE @sql NVARCHAR(2000) >> > DECLARE @Delete NVARCHAR(40) >> > >> > SET @Delete = 'TRUNCATE TABLE ' + @TableName >> > >> > EXECUTE sp_executesql @Delete >> > >> > SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM >> > OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; >> > *.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName + >> > ''')' >> > >> > EXECUTE sp_executesql @sql >> > >> > This is an exemple of my schema.ini >> > >> > [TDE003.csv] >> > ColNameHeader=False >> > Format=Delimited(;) >> > MaxScanRows=0 >> > CharacterSet=ANSI >> > COL1=no_empl char width 12 >> > COL2=no_rrr char width 12 >> > COL3=datda_rrr_perio date width 10 >> > COL4=datad_empl_rrr date width 10 >> > COL5=dat_ret_empl_rrr date width 10 >> > >> > Thereafter, there are several SP which are occupied of placing this >> > information in the working tables. On my laptop, all functions >> > correctly. >> > I have two clients. One of them has many problems to import files >> > which >> > contain dates. Sometime it works, sometime it don't. For a reason that >> > I >> > am unaware of, all the date fields that are link to a date in the csv >> > file >> > are Null in the temporary tables. However, his laptop has the same >> > configurations as mine. I don't understands.... Why this difference? >> > and >> > most of all, where to look for a solution??????? >> > >> > I need help >> > Thank you very much >> > Marc R. >> > >> >> >>
Hey ...
I have not gone through your mail .. but just giving you one hint .. may be it works. please check the datetime format of sql server and your machine. it could be different. Regards, kishor [quoted text, click to view] "Uri Dimant" wrote:
> Mark > I did some testing and it wokrd just fine. See on schema.ini file I have > changed for datetime columns > EXEC sp_addlinkedserver txtfile, 'Jet 4.0', > 'Microsoft.Jet.OLEDB.4.0', > 'c:\', > NULL, > 'Text' > select * > into #temp > from txtfile...TDE003#csv > > > select * from #temp > go > drop table #temp > > [TDE003.csv] > ColNameHeader=False > Format=Delimited(;) > DateTimeFormat=yyyy-mm-dd > MaxScanRows=0 > CharacterSet=ANSI > COL1=no_empl Text > COL2=no_rrr Text > COL3=datda_rrr_perio DateTime > COL4=datad_empl_rrr DateTime > COL5=dat_ret_empl_rrr DateTime > > > > > > > > > > > > > > > > > > > > "Marc Robitaille" <marc.marie AT globetrotter.net.del> wrote in message > news:ud6AdeS6HHA.3740@TK2MSFTNGP02.phx.gbl... > > Hello group > > > > I need Assistance on this one. I have a VB6 application which imports csv > > data in a database. There are 24 csv files. Some files contain dates like > > this example > > > > ENL80071666;REG00024;1999-01-01;1999-06-03;1999-12-31 > > ENL80071666;REG00024;2000-01-01;2000-01-01;2000-12-31 > > ENL80071666;REG00024;2001-01-01;2001-01-01;2001-12-31 > > ENL80071666;REG00024;2002-01-01;2002-01-01;2002-12-31 > > ENL80071666;REG00024;2003-01-01;2003-01-01;2003-03-31 > > > > They are imported in temporary tables with the help of this SP. > > > > set ANSI_NULLS ON > > set QUOTED_IDENTIFIER ON > > go > > > > ALTER PROCEDURE [dbo].[I_TDE_Imports] > > @FilePath VARCHAR(500), > > @FileName VARCHAR(40), > > @TableName VARCHAR(6) > > AS > > > > DECLARE @sql NVARCHAR(2000) > > DECLARE @Delete NVARCHAR(40) > > > > SET @Delete = 'TRUNCATE TABLE ' + @TableName > > > > EXECUTE sp_executesql @Delete > > > > SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM > > OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; > > *.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName + > > ''')' > > > > EXECUTE sp_executesql @sql > > > > This is an exemple of my schema.ini > > > > [TDE003.csv] > > ColNameHeader=False > > Format=Delimited(;) > > MaxScanRows=0 > > CharacterSet=ANSI > > COL1=no_empl char width 12 > > COL2=no_rrr char width 12 > > COL3=datda_rrr_perio date width 10 > > COL4=datad_empl_rrr date width 10 > > COL5=dat_ret_empl_rrr date width 10 > > > > Thereafter, there are several SP which are occupied of placing this > > information in the working tables. On my laptop, all functions correctly. > > I have two clients. One of them has many problems to import files which > > contain dates. Sometime it works, sometime it don't. For a reason that I > > am unaware of, all the date fields that are link to a date in the csv file > > are Null in the temporary tables. However, his laptop has the same > > configurations as mine. I don't understands.... Why this difference? and > > most of all, where to look for a solution??????? > > > > I need help > > Thank you very much > > Marc R. > > > >
Mark
I did some testing and it wokrd just fine. See on schema.ini file I have changed for datetime columns EXEC sp_addlinkedserver txtfile, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:\', NULL, 'Text' select * into #temp from txtfile...TDE003#csv select * from #temp go drop table #temp [TDE003.csv] ColNameHeader=False Format=Delimited(;) DateTimeFormat=yyyy-mm-dd MaxScanRows=0 CharacterSet=ANSI COL1=no_empl Text COL2=no_rrr Text COL3=datda_rrr_perio DateTime COL4=datad_empl_rrr DateTime COL5=dat_ret_empl_rrr DateTime [quoted text, click to view] "Marc Robitaille" <marc.marie AT globetrotter.net.del> wrote in message news:ud6AdeS6HHA.3740@TK2MSFTNGP02.phx.gbl... > Hello group > > I need Assistance on this one. I have a VB6 application which imports csv > data in a database. There are 24 csv files. Some files contain dates like > this example > > ENL80071666;REG00024;1999-01-01;1999-06-03;1999-12-31 > ENL80071666;REG00024;2000-01-01;2000-01-01;2000-12-31 > ENL80071666;REG00024;2001-01-01;2001-01-01;2001-12-31 > ENL80071666;REG00024;2002-01-01;2002-01-01;2002-12-31 > ENL80071666;REG00024;2003-01-01;2003-01-01;2003-03-31 > > They are imported in temporary tables with the help of this SP. > > set ANSI_NULLS ON > set QUOTED_IDENTIFIER ON > go > > ALTER PROCEDURE [dbo].[I_TDE_Imports] > @FilePath VARCHAR(500), > @FileName VARCHAR(40), > @TableName VARCHAR(6) > AS > > DECLARE @sql NVARCHAR(2000) > DECLARE @Delete NVARCHAR(40) > > SET @Delete = 'TRUNCATE TABLE ' + @TableName > > EXECUTE sp_executesql @Delete > > SET @sql = 'INSERT INTO ' + @TableName + ' SELECT * FROM > OPENROWSET(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; > *.csv)};DefaultDir=' + @FilePath + ';'',''SELECT * FROM ' + @FileName + > ''')' > > EXECUTE sp_executesql @sql > > This is an exemple of my schema.ini > > [TDE003.csv] > ColNameHeader=False > Format=Delimited(;) > MaxScanRows=0 > CharacterSet=ANSI > COL1=no_empl char width 12 > COL2=no_rrr char width 12 > COL3=datda_rrr_perio date width 10 > COL4=datad_empl_rrr date width 10 > COL5=dat_ret_empl_rrr date width 10 > > Thereafter, there are several SP which are occupied of placing this > information in the working tables. On my laptop, all functions correctly. > I have two clients. One of them has many problems to import files which > contain dates. Sometime it works, sometime it don't. For a reason that I > am unaware of, all the date fields that are link to a date in the csv file > are Null in the temporary tables. However, his laptop has the same > configurations as mine. I don't understands.... Why this difference? and > most of all, where to look for a solution??????? > > I need help > Thank you very much > Marc R. >
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |