|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Altering a DBF FileLanguage: VB.NET Fox Pro Driver Version: 9.0.0.3504 Problem: I currently have a problem altering a DBF file. I do not get any syntax errors when running the program. However, after I alter the table and open microsoft excel to look at any changes; I get the following error: "This file is not in a recognizable format" If I do open the file in excel it looks like its not formatted. Further Information: I am not sure if I need to pack the DBF after altering it (add a column), however, I did pack it to see if it would solve my problem but that did not work. Why am I trying to view the file in microsoft excel? More than likely the user will be periodically open the DBF in excel for viewing. Code: (note I did not paste all the code just code I felt was needed) cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny None;Extended Properties="""";Exclusive=ON" qy = "Alter Table " + fname + " ADD MILES INT NULL" Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, ByVal FileExt As String) Dim cs As String Dim cn As OleDbConnection Dim dc As OleDbCommand cs = Connection_String(FilePath, FileExt) cn = New OleDbConnection(cs) cn.Open() dc = New OleDbCommand(qy, cn) dc.ExecuteNonQuery() cn.Close() End Sub Hi Jimmy,
I assume that if you open the target dbf in Excel before this code is run, there are no issues; and that if you open the dbf in Foxpro after this code has been run, there are no issues? Will the "after-dbf" become Excel readable if you make other changes and save/save as from within Foxpro? It's been a while since I've used Foxpro; can you confirm that Foxpro's Alter syntax doesn't require the COLUMN keyword: qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL" Regards, Keith JimmyKoolPantz wrote: Show quoteHide quote > IDE: Visual Studio 2005 > Language: VB.NET > Fox Pro Driver Version: 9.0.0.3504 > > Problem: I currently have a problem altering a DBF file. I do not get > any syntax errors when running the program. However, after I alter the > > table and open microsoft excel to look at any changes; I get the > following error: "This file is not in a recognizable format" If I do > open the file in excel it looks like its not formatted. > > > Further Information: I am not sure if I need to pack the DBF after > altering it (add a column), however, I did pack it to see if it would > solve my problem but that did not work. > > > Why am I trying to view the file in microsoft excel? > More than likely the user will be periodically open the DBF in excel > for viewing. > > > Code: (note I did not paste all the code just code I felt was needed) > > > cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny > None;Extended Properties="""";Exclusive=ON" > > > qy = "Alter Table " + fname + " ADD MILES INT NULL" > > > Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, > ByVal FileExt As String) > > > Dim cs As String > Dim cn As OleDbConnection > Dim dc As OleDbCommand > > > cs = Connection_String(FilePath, FileExt) > cn = New OleDbConnection(cs) > cn.Open() > dc = New OleDbCommand(qy, cn) > dc.ExecuteNonQuery() > cn.Close() > > > End Sub I used the key word "COLUMN" and I get the same results.. the program
processes however, I am not able to view the dbf using excel when finished processing.. it looks like unformated text. I frequently have a hard time explaining myself and with not much programming experience this could be very misleading. Basically, what I am trying to do is to add a column to a dbf file that already contains data. The dbf files were generated from microsoft access 2003. I will not be using foxpro to process these dbf files. However, through research, and try and error. It's my assumption that the only driver that I can use to add a column to a dbf file is the foxpro driver. I've tryed oledbf, odbc drives that are not foxpro and I get an error. However, I did not use the keyword "Null" in my querry statement. I might just try and see if a non foxpro driver will work with the "Null" keyword. Any advice would greatly be appreciated. kgerritsen wrote: Show quoteHide quote > Hi Jimmy, > > I assume that if you open the target dbf in Excel before this code is > run, there are no issues; and that if you open the dbf in Foxpro after > this code has been run, there are no issues? Will the "after-dbf" > become Excel readable if you make other changes and save/save as from > within Foxpro? > > It's been a while since I've used Foxpro; can you confirm that Foxpro's > Alter syntax doesn't require the COLUMN keyword: > > qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL" > > Regards, > Keith > > JimmyKoolPantz wrote: > > IDE: Visual Studio 2005 > > Language: VB.NET > > Fox Pro Driver Version: 9.0.0.3504 > > > > Problem: I currently have a problem altering a DBF file. I do not get > > any syntax errors when running the program. However, after I alter the > > > > table and open microsoft excel to look at any changes; I get the > > following error: "This file is not in a recognizable format" If I do > > open the file in excel it looks like its not formatted. > > > > > > Further Information: I am not sure if I need to pack the DBF after > > altering it (add a column), however, I did pack it to see if it would > > solve my problem but that did not work. > > > > > > Why am I trying to view the file in microsoft excel? > > More than likely the user will be periodically open the DBF in excel > > for viewing. > > > > > > Code: (note I did not paste all the code just code I felt was needed) > > > > > > cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny > > None;Extended Properties="""";Exclusive=ON" > > > > > > qy = "Alter Table " + fname + " ADD MILES INT NULL" > > > > > > Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, > > ByVal FileExt As String) > > > > > > Dim cs As String > > Dim cn As OleDbConnection > > Dim dc As OleDbCommand > > > > > > cs = Connection_String(FilePath, FileExt) > > cn = New OleDbConnection(cs) > > cn.Open() > > dc = New OleDbCommand(qy, cn) > > dc.ExecuteNonQuery() > > cn.Close() > > > > > > End Sub It could very well be that .net is causing the dbf to be unreadable by
excel, but .net and foxpro can still read it. I like Keith's idea about altering the table from within foxpro, then see if it is readable by excel. Which version of foxpro is the original table from? And which version of excel are you using? It could be that an older table that is readable by your version of excel is altered to become a newer version that excel can no longer read. If you are using a new version of foxpro, then it may also cause excel to fail in the read. Just my 2 cents. T JimmyKoolPantz wrote: Show quoteHide quote >I used the key word "COLUMN" and I get the same results.. the program >processes however, I am not able to view the dbf using excel when >finished processing.. it looks like unformated text. > >I frequently have a hard time explaining myself and with not much >programming experience this could be very misleading. > >Basically, what I am trying to do is to add a column to a dbf file that >already contains data. The dbf files were generated from microsoft >access 2003. I will not be using foxpro to process these dbf files. >However, through research, and try and error. It's my assumption that >the only driver that I can use to add a column to a dbf file is the >foxpro driver. I've tryed oledbf, odbc drives that are not foxpro and >I get an error. However, I did not use the keyword "Null" in my querry >statement. I might just try and see if a non foxpro driver will work >with the "Null" keyword. > >Any advice would greatly be appreciated. >kgerritsen wrote: > > >>Hi Jimmy, >> >>I assume that if you open the target dbf in Excel before this code is >>run, there are no issues; and that if you open the dbf in Foxpro after >>this code has been run, there are no issues? Will the "after-dbf" >>become Excel readable if you make other changes and save/save as from >>within Foxpro? >> >>It's been a while since I've used Foxpro; can you confirm that Foxpro's >>Alter syntax doesn't require the COLUMN keyword: >> >>qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL" >> >>Regards, >>Keith >> >>JimmyKoolPantz wrote: >> >> >>>IDE: Visual Studio 2005 >>>Language: VB.NET >>>Fox Pro Driver Version: 9.0.0.3504 >>> >>>Problem: I currently have a problem altering a DBF file. I do not get >>>any syntax errors when running the program. However, after I alter the >>> >>>table and open microsoft excel to look at any changes; I get the >>>following error: "This file is not in a recognizable format" If I do >>>open the file in excel it looks like its not formatted. >>> >>> >>>Further Information: I am not sure if I need to pack the DBF after >>>altering it (add a column), however, I did pack it to see if it would >>>solve my problem but that did not work. >>> >>> >>>Why am I trying to view the file in microsoft excel? >>>More than likely the user will be periodically open the DBF in excel >>>for viewing. >>> >>> >>>Code: (note I did not paste all the code just code I felt was needed) >>> >>> >>>cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny >>>None;Extended Properties="""";Exclusive=ON" >>> >>> >>>qy = "Alter Table " + fname + " ADD MILES INT NULL" >>> >>> >>>Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, >>>ByVal FileExt As String) >>> >>> >>> Dim cs As String >>> Dim cn As OleDbConnection >>> Dim dc As OleDbCommand >>> >>> >>> cs = Connection_String(FilePath, FileExt) >>> cn = New OleDbConnection(cs) >>> cn.Open() >>> dc = New OleDbCommand(qy, cn) >>> dc.ExecuteNonQuery() >>> cn.Close() >>> >>> >>> End Sub >>> >>> > > > The DBF that I am working with was not generated from foxpro. We are
not using foxpro at work. We get our files in different formats (text, xls, csv) and we import them into a microsoft access table, then run a query in access, and then export them as dbf files. I think I am going to put this project on hold for a few days. Does anyone have any advise? If I can not add a column to a dbf file that contains data then what are my alternative options? Things I do know/need: 1. I need to add a column to a dbf file 2. The user needs to be able to open the file using microsoft excel after the column has been added. 3. Creating the program in foxpro is not an option. 4. Processing needs to be fast. 5. The files need to be a dbf file not any other type of file. The only solution I can think of now is to create a new dbf file in binary mode with the added column and then write every record to the file using the .net binary writer. However, I just don't see the logic behind it. All that needs to be added is a new column. I'm looking for professional advice on what I need to do now, I don't have the experience to determine what is best, all I am doing now is basically running in circles. Just to let you know I am not getting paid for this program I am doing this for self knowledge. Nor, I am not asking anyone to code this program, I am just looking for guidence. tomb wrote: Show quoteHide quote > It could very well be that .net is causing the dbf to be unreadable by > excel, but .net and foxpro can still read it. I like Keith's idea about > altering the table from within foxpro, then see if it is readable by excel. > > Which version of foxpro is the original table from? And which version > of excel are you using? It could be that an older table that is > readable by your version of excel is altered to become a newer version > that excel can no longer read. If you are using a new version of > foxpro, then it may also cause excel to fail in the read. > > Just my 2 cents. > > T > > JimmyKoolPantz wrote: > > >I used the key word "COLUMN" and I get the same results.. the program > >processes however, I am not able to view the dbf using excel when > >finished processing.. it looks like unformated text. > > > >I frequently have a hard time explaining myself and with not much > >programming experience this could be very misleading. > > > >Basically, what I am trying to do is to add a column to a dbf file that > >already contains data. The dbf files were generated from microsoft > >access 2003. I will not be using foxpro to process these dbf files. > >However, through research, and try and error. It's my assumption that > >the only driver that I can use to add a column to a dbf file is the > >foxpro driver. I've tryed oledbf, odbc drives that are not foxpro and > >I get an error. However, I did not use the keyword "Null" in my querry > >statement. I might just try and see if a non foxpro driver will work > >with the "Null" keyword. > > > >Any advice would greatly be appreciated. > >kgerritsen wrote: > > > > > >>Hi Jimmy, > >> > >>I assume that if you open the target dbf in Excel before this code is > >>run, there are no issues; and that if you open the dbf in Foxpro after > >>this code has been run, there are no issues? Will the "after-dbf" > >>become Excel readable if you make other changes and save/save as from > >>within Foxpro? > >> > >>It's been a while since I've used Foxpro; can you confirm that Foxpro's > >>Alter syntax doesn't require the COLUMN keyword: > >> > >>qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL" > >> > >>Regards, > >>Keith > >> > >>JimmyKoolPantz wrote: > >> > >> > >>>IDE: Visual Studio 2005 > >>>Language: VB.NET > >>>Fox Pro Driver Version: 9.0.0.3504 > >>> > >>>Problem: I currently have a problem altering a DBF file. I do not get > >>>any syntax errors when running the program. However, after I alter the > >>> > >>>table and open microsoft excel to look at any changes; I get the > >>>following error: "This file is not in a recognizable format" If I do > >>>open the file in excel it looks like its not formatted. > >>> > >>> > >>>Further Information: I am not sure if I need to pack the DBF after > >>>altering it (add a column), however, I did pack it to see if it would > >>>solve my problem but that did not work. > >>> > >>> > >>>Why am I trying to view the file in microsoft excel? > >>>More than likely the user will be periodically open the DBF in excel > >>>for viewing. > >>> > >>> > >>>Code: (note I did not paste all the code just code I felt was needed) > >>> > >>> > >>>cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny > >>>None;Extended Properties="""";Exclusive=ON" > >>> > >>> > >>>qy = "Alter Table " + fname + " ADD MILES INT NULL" > >>> > >>> > >>>Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, > >>>ByVal FileExt As String) > >>> > >>> > >>> Dim cs As String > >>> Dim cn As OleDbConnection > >>> Dim dc As OleDbCommand > >>> > >>> > >>> cs = Connection_String(FilePath, FileExt) > >>> cn = New OleDbConnection(cs) > >>> cn.Open() > >>> dc = New OleDbCommand(qy, cn) > >>> dc.ExecuteNonQuery() > >>> cn.Close() > >>> > >>> > >>> End Sub > >>> > >>> > > > > > > As you may know there isn't a single ".DBF" format. Rather there are a few
slightly differing ones with the greatest difference probably being the FoxPro versions. In order to add new features the various vendors who used ..DBF files would change the header record here and there. Other languages and utilities (like Excel) check the format to various degrees and if it can't recognize it then it declares it isn't dBASE or in some cases that it is corrupt. What probably happened in the FoxPro case is that it created a "FoxPro" .DBF file by adding a number of new fields in the header record. This can be supressed (in FoxPro itself it is done by adding TYPE FOXPLUS or TYPE FOX2X. It could be that you can control the type and perhaps add the column when you export it from Access, you might look into that. The other possibility is that the FoxPro driver you are using in .Net supports the "unmodified" ..DBF format (check for a setting). Most probably the NULL option won't be allowed as standard .DBF files have no support for NULL values. This is one of those features added to FoxPro .DBF's that made them non-standard. Lastly if you just want to open the file in Excel you might consider outputting CSV files from Access. Is it important that it end up a .DBF file? If so consider calling a short utility program (written in Clipper, XHarbour, FoxPro or any other dBASE-compatible language) that will add your column for you without modifying the header record unnecessarily. Tom Show quoteHide quote "JimmyKoolPantz" <kohl.m***@gmail.com> wrote in message news:1162677422.076140.212320@i42g2000cwa.googlegroups.com... > The DBF that I am working with was not generated from foxpro. We are > not using foxpro at work. We get our files in different formats (text, > xls, csv) and we import them into a microsoft access table, then run a > query in access, and then export them as dbf files. > > I think I am going to put this project on hold for a few days. Does > anyone have any advise? If I can not add a column to a dbf file that > contains data then what are my alternative options? > > Things I do know/need: > > 1. I need to add a column to a dbf file > 2. The user needs to be able to open the file using microsoft excel > after the column has been added. > 3. Creating the program in foxpro is not an option. > 4. Processing needs to be fast. > 5. The files need to be a dbf file not any other type of file. > > The only solution I can think of now is to create a new dbf file in > binary mode with the added column and then write every record to the > file using the .net binary writer. However, I just don't see the logic > behind it. All that needs to be added is a new column. I'm looking > for professional advice on what I need to do now, I don't have the > experience to determine what is best, all I am doing now is basically > running in circles. Just to let you know I am not getting paid for > this program I am doing this for self knowledge. Nor, I am not asking > anyone to code this program, I am just looking for guidence. > > > tomb wrote: >> It could very well be that .net is causing the dbf to be unreadable by >> excel, but .net and foxpro can still read it. I like Keith's idea about >> altering the table from within foxpro, then see if it is readable by >> excel. >> >> Which version of foxpro is the original table from? And which version >> of excel are you using? It could be that an older table that is >> readable by your version of excel is altered to become a newer version >> that excel can no longer read. If you are using a new version of >> foxpro, then it may also cause excel to fail in the read. >> >> Just my 2 cents. >> >> T >> >> JimmyKoolPantz wrote: >> >> >I used the key word "COLUMN" and I get the same results.. the program >> >processes however, I am not able to view the dbf using excel when >> >finished processing.. it looks like unformated text. >> > >> >I frequently have a hard time explaining myself and with not much >> >programming experience this could be very misleading. >> > >> >Basically, what I am trying to do is to add a column to a dbf file that >> >already contains data. The dbf files were generated from microsoft >> >access 2003. I will not be using foxpro to process these dbf files. >> >However, through research, and try and error. It's my assumption that >> >the only driver that I can use to add a column to a dbf file is the >> >foxpro driver. I've tryed oledbf, odbc drives that are not foxpro and >> >I get an error. However, I did not use the keyword "Null" in my querry >> >statement. I might just try and see if a non foxpro driver will work >> >with the "Null" keyword. >> > >> >Any advice would greatly be appreciated. >> >kgerritsen wrote: >> > >> > >> >>Hi Jimmy, >> >> >> >>I assume that if you open the target dbf in Excel before this code is >> >>run, there are no issues; and that if you open the dbf in Foxpro after >> >>this code has been run, there are no issues? Will the "after-dbf" >> >>become Excel readable if you make other changes and save/save as from >> >>within Foxpro? >> >> >> >>It's been a while since I've used Foxpro; can you confirm that Foxpro's >> >>Alter syntax doesn't require the COLUMN keyword: >> >> >> >>qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL" >> >> >> >>Regards, >> >>Keith >> >> >> >>JimmyKoolPantz wrote: >> >> >> >> >> >>>IDE: Visual Studio 2005 >> >>>Language: VB.NET >> >>>Fox Pro Driver Version: 9.0.0.3504 >> >>> >> >>>Problem: I currently have a problem altering a DBF file. I do not get >> >>>any syntax errors when running the program. However, after I alter >> >>>the >> >>> >> >>>table and open microsoft excel to look at any changes; I get the >> >>>following error: "This file is not in a recognizable format" If I do >> >>>open the file in excel it looks like its not formatted. >> >>> >> >>> >> >>>Further Information: I am not sure if I need to pack the DBF after >> >>>altering it (add a column), however, I did pack it to see if it would >> >>>solve my problem but that did not work. >> >>> >> >>> >> >>>Why am I trying to view the file in microsoft excel? >> >>>More than likely the user will be periodically open the DBF in excel >> >>>for viewing. >> >>> >> >>> >> >>>Code: (note I did not paste all the code just code I felt was needed) >> >>> >> >>> >> >>>cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny >> >>>None;Extended Properties="""";Exclusive=ON" >> >>> >> >>> >> >>>qy = "Alter Table " + fname + " ADD MILES INT NULL" >> >>> >> >>> >> >>>Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, >> >>>ByVal FileExt As String) >> >>> >> >>> >> >>> Dim cs As String >> >>> Dim cn As OleDbConnection >> >>> Dim dc As OleDbCommand >> >>> >> >>> >> >>> cs = Connection_String(FilePath, FileExt) >> >>> cn = New OleDbConnection(cs) >> >>> cn.Open() >> >>> dc = New OleDbCommand(qy, cn) >> >>> dc.ExecuteNonQuery() >> >>> cn.Close() >> >>> >> >>> >> >>> End Sub >> >>> >> >>> >> > >> > >> > > Thanks Tom,
Yes, it is important that the file is a DBF file. The reason being is that the primary application that we use to process files uses DBF file format. So, the file format must be a DBF file. I'm sure there are 3rd party applications that do what I want to do such as xbase, however I dont have the money to go out and buy their product. On the other hand, I have the mentallity if someone else can do it then I can at least try and do it. I just haven't found out how yet :). Whats strange is I downloaded a dbf viewer, just to look at the file, and I can view it after the column was added. Then I exported the file as a dbf file just to see if I can open it in excel. I was able to open it in excel, however, I noticed there was an additional column added when I viewed it. The name of the column is "_NullFlags". Do you know anything about this column? I looked at a DBF file structure and did not see this mentioned anywhere. Also, I just wanted to mention that adding a field to the dbf file before exporting it from Microsoft Access is not an option. I know it is easy that way, but the users would not go for it. Basically the columns that I need to add are towards the end of processing. Is there a specific postion that microsoft excel looks at to detemine if the file is a dbase file? Is so then I could go out and modify that position. Iol, Im sure its not that easy. I'm really starting to hate dbf files. They have cost me so much time, and stress. I hope I figure this out one day. Tom Leylan wrote: Show quoteHide quote > As you may know there isn't a single ".DBF" format. Rather there are a few > slightly differing ones with the greatest difference probably being the > FoxPro versions. In order to add new features the various vendors who used > .DBF files would change the header record here and there. Other languages > and utilities (like Excel) check the format to various degrees and if it > can't recognize it then it declares it isn't dBASE or in some cases that it > is corrupt. What probably happened in the FoxPro case is that it created a > "FoxPro" .DBF file by adding a number of new fields in the header record. > This can be supressed (in FoxPro itself it is done by adding TYPE FOXPLUS or > TYPE FOX2X. > > It could be that you can control the type and perhaps add the column when > you export it from Access, you might look into that. The other possibility > is that the FoxPro driver you are using in .Net supports the "unmodified" > .DBF format (check for a setting). Most probably the NULL option won't be > allowed as standard .DBF files have no support for NULL values. This is one > of those features added to FoxPro .DBF's that made them non-standard. > > Lastly if you just want to open the file in Excel you might consider > outputting CSV files from Access. Is it important that it end up a .DBF > file? If so consider calling a short utility program (written in Clipper, > XHarbour, FoxPro or any other dBASE-compatible language) that will add your > column for you without modifying the header record unnecessarily. > > Tom > > > > "JimmyKoolPantz" <kohl.m***@gmail.com> wrote in message > news:1162677422.076140.212320@i42g2000cwa.googlegroups.com... > > The DBF that I am working with was not generated from foxpro. We are > > not using foxpro at work. We get our files in different formats (text, > > xls, csv) and we import them into a microsoft access table, then run a > > query in access, and then export them as dbf files. > > > > I think I am going to put this project on hold for a few days. Does > > anyone have any advise? If I can not add a column to a dbf file that > > contains data then what are my alternative options? > > > > Things I do know/need: > > > > 1. I need to add a column to a dbf file > > 2. The user needs to be able to open the file using microsoft excel > > after the column has been added. > > 3. Creating the program in foxpro is not an option. > > 4. Processing needs to be fast. > > 5. The files need to be a dbf file not any other type of file. > > > > The only solution I can think of now is to create a new dbf file in > > binary mode with the added column and then write every record to the > > file using the .net binary writer. However, I just don't see the logic > > behind it. All that needs to be added is a new column. I'm looking > > for professional advice on what I need to do now, I don't have the > > experience to determine what is best, all I am doing now is basically > > running in circles. Just to let you know I am not getting paid for > > this program I am doing this for self knowledge. Nor, I am not asking > > anyone to code this program, I am just looking for guidence. > > > > > > tomb wrote: > >> It could very well be that .net is causing the dbf to be unreadable by > >> excel, but .net and foxpro can still read it. I like Keith's idea about > >> altering the table from within foxpro, then see if it is readable by > >> excel. > >> > >> Which version of foxpro is the original table from? And which version > >> of excel are you using? It could be that an older table that is > >> readable by your version of excel is altered to become a newer version > >> that excel can no longer read. If you are using a new version of > >> foxpro, then it may also cause excel to fail in the read. > >> > >> Just my 2 cents. > >> > >> T > >> > >> JimmyKoolPantz wrote: > >> > >> >I used the key word "COLUMN" and I get the same results.. the program > >> >processes however, I am not able to view the dbf using excel when > >> >finished processing.. it looks like unformated text. > >> > > >> >I frequently have a hard time explaining myself and with not much > >> >programming experience this could be very misleading. > >> > > >> >Basically, what I am trying to do is to add a column to a dbf file that > >> >already contains data. The dbf files were generated from microsoft > >> >access 2003. I will not be using foxpro to process these dbf files. > >> >However, through research, and try and error. It's my assumption that > >> >the only driver that I can use to add a column to a dbf file is the > >> >foxpro driver. I've tryed oledbf, odbc drives that are not foxpro and > >> >I get an error. However, I did not use the keyword "Null" in my querry > >> >statement. I might just try and see if a non foxpro driver will work > >> >with the "Null" keyword. > >> > > >> >Any advice would greatly be appreciated. > >> >kgerritsen wrote: > >> > > >> > > >> >>Hi Jimmy, > >> >> > >> >>I assume that if you open the target dbf in Excel before this code is > >> >>run, there are no issues; and that if you open the dbf in Foxpro after > >> >>this code has been run, there are no issues? Will the "after-dbf" > >> >>become Excel readable if you make other changes and save/save as from > >> >>within Foxpro? > >> >> > >> >>It's been a while since I've used Foxpro; can you confirm that Foxpro's > >> >>Alter syntax doesn't require the COLUMN keyword: > >> >> > >> >>qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL" > >> >> > >> >>Regards, > >> >>Keith > >> >> > >> >>JimmyKoolPantz wrote: > >> >> > >> >> > >> >>>IDE: Visual Studio 2005 > >> >>>Language: VB.NET > >> >>>Fox Pro Driver Version: 9.0.0.3504 > >> >>> > >> >>>Problem: I currently have a problem altering a DBF file. I do not get > >> >>>any syntax errors when running the program. However, after I alter > >> >>>the > >> >>> > >> >>>table and open microsoft excel to look at any changes; I get the > >> >>>following error: "This file is not in a recognizable format" If I do > >> >>>open the file in excel it looks like its not formatted. > >> >>> > >> >>> > >> >>>Further Information: I am not sure if I need to pack the DBF after > >> >>>altering it (add a column), however, I did pack it to see if it would > >> >>>solve my problem but that did not work. > >> >>> > >> >>> > >> >>>Why am I trying to view the file in microsoft excel? > >> >>>More than likely the user will be periodically open the DBF in excel > >> >>>for viewing. > >> >>> > >> >>> > >> >>>Code: (note I did not paste all the code just code I felt was needed) > >> >>> > >> >>> > >> >>>cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share Deny > >> >>>None;Extended Properties="""";Exclusive=ON" > >> >>> > >> >>> > >> >>>qy = "Alter Table " + fname + " ADD MILES INT NULL" > >> >>> > >> >>> > >> >>>Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, > >> >>>ByVal FileExt As String) > >> >>> > >> >>> > >> >>> Dim cs As String > >> >>> Dim cn As OleDbConnection > >> >>> Dim dc As OleDbCommand > >> >>> > >> >>> > >> >>> cs = Connection_String(FilePath, FileExt) > >> >>> cn = New OleDbConnection(cs) > >> >>> cn.Open() > >> >>> dc = New OleDbCommand(qy, cn) > >> >>> dc.ExecuteNonQuery() > >> >>> cn.Close() > >> >>> > >> >>> > >> >>> End Sub > >> >>> > >> >>> > >> > > >> > > >> > > > Not 3rd party applications... if you can write in VB.Net you can write a 20
line program to modify the structure of a .DBF file using say XHarbour (which is free) and it will compile into a 32-bit .EXE or borrow a copy (or have somebody write it) using FoxPro which will also result in an .EXE. Come to think of it in either case you could generate a .DLL file instead. There is also a scripting language version of XHarbour (XBScript I believe it is called) which conforms to the MS scripting language conventions which would work as well. That way you wouldn't even compile it, you can view/edit the script with any text editor. None of these solutions cost money, just time. Of course it would be cleaner to do it in .Net directly. The _NullFlags column is an indication that it isn't standard DBF format. As I mentioned ..DBF files don't understand the concept of NULL, empty fields are always the empty equivalent of their datatype, e.g. "", 0, False. FoxPro introduced all sorts of things to beef up the .DBF format I assume to confirm to SQL and eventually .Net. I don't know which .DBF viewer you dl'd but it is quite possible it just doesn't checks the header for the presence of things it requires and ignores things it doesn't. In other words it won't display the content of the _NullFlags column (if you display the structure) but it doesn't care that it is present. I have no idea what Excel does to determine if the .DBF file is intact but it could do a little or a lot. If I had your problem I would begin by trying to determine if the DBF driver has some sort of version or compatibility flag which you could set. If it could produce a bare bones (dare I call it "standard") .DBF then you're home free. Second I'd opt for the idea of passing it through a process I wrote in a dBASE-compatible language and as I mention you could produce a .DLL so you could it call it directly from your .Net program. Third I might opt to produce the .DBF file manually which you mentioned in one of your earlier messages. It isn't a binary format BTW. It's a text file with a special header at the top followed by a series of fixed length records. The structure is well documented and public knowledge. Oh there is another easy solution. Is the resulting .DBF file a constant format, in other words do you export a .DBF with a common layout and then add a particular extra column to it? You can create an empty .DBF with the structure you want (probably using the viewer you dl'd and if not then using any number of free tools). This empty .DBF would have the structure you want to end up with including the extra column(s). You export the one without the column using Access and then open the template using .Net and import the data from the Access .DBF. When you append records this way; fields that aren't present in the source are just left empty in the destination. At that point your data has the proper structure and of course you can fill the new field under program control once the import is done. I wouldn't be too hard on the .DBF format. It isn't ideal but then it was designed in the late 70's and nobody figured it would be opened using .Net and Excel at that time :-) There is no central authority to improve it so everybody went their separate way but unfortunately they kept the same ".DBF" extension. The dBASE index files and memo files have undergone customization as well but in those cases the companies actually did change the extension so there aren't quite so many problems. Tom Show quoteHide quote "JimmyKoolPantz" <kohl.m***@gmail.com> wrote in message news:1162689774.873740.170390@i42g2000cwa.googlegroups.com... > Thanks Tom, > > Yes, it is important that the file is a DBF file. The reason being is > that the primary application that we use to process files uses DBF file > format. So, the file format must be a DBF file. > > I'm sure there are 3rd party applications that do what I want to do > such as xbase, however I dont have the money to go out and buy their > product. On the other hand, I have the mentallity if someone else can > do it then I can at least try and do it. I just haven't found out how > yet :). > > Whats strange is I downloaded a dbf viewer, just to look at the file, > and I can view it after the column was added. Then I exported the file > as a dbf file just to see if I can open it in excel. I was able to > open it in excel, however, I noticed there was an additional column > added when I viewed it. The name of the column is "_NullFlags". Do > you know anything about this column? I looked at a DBF file structure > and did not see this mentioned anywhere. > > Also, I just wanted to mention that adding a field to the dbf file > before exporting it from Microsoft Access is not an option. I know it > is easy that way, but the users would not go for it. Basically the > columns that I need to add are towards the end of processing. > > Is there a specific postion that microsoft excel looks at to detemine > if the file is a dbase file? Is so then I could go out and modify that > position. Iol, Im sure its not that easy. I'm really starting to hate > dbf files. They have cost me so much time, and stress. I hope I > figure this out one day. > > > Tom Leylan wrote: >> As you may know there isn't a single ".DBF" format. Rather there are a >> few >> slightly differing ones with the greatest difference probably being the >> FoxPro versions. In order to add new features the various vendors who >> used >> .DBF files would change the header record here and there. Other >> languages >> and utilities (like Excel) check the format to various degrees and if it >> can't recognize it then it declares it isn't dBASE or in some cases that >> it >> is corrupt. What probably happened in the FoxPro case is that it created >> a >> "FoxPro" .DBF file by adding a number of new fields in the header record. >> This can be supressed (in FoxPro itself it is done by adding TYPE FOXPLUS >> or >> TYPE FOX2X. >> >> It could be that you can control the type and perhaps add the column when >> you export it from Access, you might look into that. The other >> possibility >> is that the FoxPro driver you are using in .Net supports the "unmodified" >> .DBF format (check for a setting). Most probably the NULL option won't >> be >> allowed as standard .DBF files have no support for NULL values. This is >> one >> of those features added to FoxPro .DBF's that made them non-standard. >> >> Lastly if you just want to open the file in Excel you might consider >> outputting CSV files from Access. Is it important that it end up a .DBF >> file? If so consider calling a short utility program (written in >> Clipper, >> XHarbour, FoxPro or any other dBASE-compatible language) that will add >> your >> column for you without modifying the header record unnecessarily. >> >> Tom >> >> >> >> "JimmyKoolPantz" <kohl.m***@gmail.com> wrote in message >> news:1162677422.076140.212320@i42g2000cwa.googlegroups.com... >> > The DBF that I am working with was not generated from foxpro. We are >> > not using foxpro at work. We get our files in different formats (text, >> > xls, csv) and we import them into a microsoft access table, then run a >> > query in access, and then export them as dbf files. >> > >> > I think I am going to put this project on hold for a few days. Does >> > anyone have any advise? If I can not add a column to a dbf file that >> > contains data then what are my alternative options? >> > >> > Things I do know/need: >> > >> > 1. I need to add a column to a dbf file >> > 2. The user needs to be able to open the file using microsoft excel >> > after the column has been added. >> > 3. Creating the program in foxpro is not an option. >> > 4. Processing needs to be fast. >> > 5. The files need to be a dbf file not any other type of file. >> > >> > The only solution I can think of now is to create a new dbf file in >> > binary mode with the added column and then write every record to the >> > file using the .net binary writer. However, I just don't see the logic >> > behind it. All that needs to be added is a new column. I'm looking >> > for professional advice on what I need to do now, I don't have the >> > experience to determine what is best, all I am doing now is basically >> > running in circles. Just to let you know I am not getting paid for >> > this program I am doing this for self knowledge. Nor, I am not asking >> > anyone to code this program, I am just looking for guidence. >> > >> > >> > tomb wrote: >> >> It could very well be that .net is causing the dbf to be unreadable by >> >> excel, but .net and foxpro can still read it. I like Keith's idea >> >> about >> >> altering the table from within foxpro, then see if it is readable by >> >> excel. >> >> >> >> Which version of foxpro is the original table from? And which version >> >> of excel are you using? It could be that an older table that is >> >> readable by your version of excel is altered to become a newer version >> >> that excel can no longer read. If you are using a new version of >> >> foxpro, then it may also cause excel to fail in the read. >> >> >> >> Just my 2 cents. >> >> >> >> T >> >> >> >> JimmyKoolPantz wrote: >> >> >> >> >I used the key word "COLUMN" and I get the same results.. the program >> >> >processes however, I am not able to view the dbf using excel when >> >> >finished processing.. it looks like unformated text. >> >> > >> >> >I frequently have a hard time explaining myself and with not much >> >> >programming experience this could be very misleading. >> >> > >> >> >Basically, what I am trying to do is to add a column to a dbf file >> >> >that >> >> >already contains data. The dbf files were generated from microsoft >> >> >access 2003. I will not be using foxpro to process these dbf files. >> >> >However, through research, and try and error. It's my assumption >> >> >that >> >> >the only driver that I can use to add a column to a dbf file is the >> >> >foxpro driver. I've tryed oledbf, odbc drives that are not foxpro >> >> >and >> >> >I get an error. However, I did not use the keyword "Null" in my >> >> >querry >> >> >statement. I might just try and see if a non foxpro driver will work >> >> >with the "Null" keyword. >> >> > >> >> >Any advice would greatly be appreciated. >> >> >kgerritsen wrote: >> >> > >> >> > >> >> >>Hi Jimmy, >> >> >> >> >> >>I assume that if you open the target dbf in Excel before this code >> >> >>is >> >> >>run, there are no issues; and that if you open the dbf in Foxpro >> >> >>after >> >> >>this code has been run, there are no issues? Will the "after-dbf" >> >> >>become Excel readable if you make other changes and save/save as >> >> >>from >> >> >>within Foxpro? >> >> >> >> >> >>It's been a while since I've used Foxpro; can you confirm that >> >> >>Foxpro's >> >> >>Alter syntax doesn't require the COLUMN keyword: >> >> >> >> >> >>qy = "Alter Table " + fname + " ADD COLUMN MILES INT NULL" >> >> >> >> >> >>Regards, >> >> >>Keith >> >> >> >> >> >>JimmyKoolPantz wrote: >> >> >> >> >> >> >> >> >>>IDE: Visual Studio 2005 >> >> >>>Language: VB.NET >> >> >>>Fox Pro Driver Version: 9.0.0.3504 >> >> >>> >> >> >>>Problem: I currently have a problem altering a DBF file. I do not >> >> >>>get >> >> >>>any syntax errors when running the program. However, after I alter >> >> >>>the >> >> >>> >> >> >>>table and open microsoft excel to look at any changes; I get the >> >> >>>following error: "This file is not in a recognizable format" If I >> >> >>>do >> >> >>>open the file in excel it looks like its not formatted. >> >> >>> >> >> >>> >> >> >>>Further Information: I am not sure if I need to pack the DBF after >> >> >>>altering it (add a column), however, I did pack it to see if it >> >> >>>would >> >> >>>solve my problem but that did not work. >> >> >>> >> >> >>> >> >> >>>Why am I trying to view the file in microsoft excel? >> >> >>>More than likely the user will be periodically open the DBF in >> >> >>>excel >> >> >>>for viewing. >> >> >>> >> >> >>> >> >> >>>Code: (note I did not paste all the code just code I felt was >> >> >>>needed) >> >> >>> >> >> >>> >> >> >>>cs = "Provider=VFPOLEDB.1;Data Source=" + FilePath + ";Mode=Share >> >> >>>Deny >> >> >>>None;Extended Properties="""";Exclusive=ON" >> >> >>> >> >> >>> >> >> >>>qy = "Alter Table " + fname + " ADD MILES INT NULL" >> >> >>> >> >> >>> >> >> >>>Public Sub UpdateDBF(ByVal FilePath As String, ByVal qy As String, >> >> >>>ByVal FileExt As String) >> >> >>> >> >> >>> >> >> >>> Dim cs As String >> >> >>> Dim cn As OleDbConnection >> >> >>> Dim dc As OleDbCommand >> >> >>> >> >> >>> >> >> >>> cs = Connection_String(FilePath, FileExt) >> >> >>> cn = New OleDbConnection(cs) >> >> >>> cn.Open() >> >> >>> dc = New OleDbCommand(qy, cn) >> >> >>> dc.ExecuteNonQuery() >> >> >>> cn.Close() >> >> >>> >> >> >>> >> >> >>> End Sub >> >> >>> >> >> >>> >> >> > >> >> > >> >> > >> > > Hi Tom,
This is exactly the case. Only FoxPro 2.6 and earlier tables are readable natively by Excel. The Integer data type and Null support were added in VFP3, and adding them changes the table structure to a Visual FoxPro "free" table. Tables in this format are only accessible via ODBC. To go further, even more data features were added in Visual FoxPro 7 and above. Tables with these features are no longer ODBC compatible; they must be accessed via OLE DB. -- Show quoteHide quoteCindy Winegarden MCSD, Microsoft Most Valuable Professional ci***@cindywinegarden.com "tomb" <t***@technetcenter.com> wrote in message news:f923h.751$3L.352@bignews2.bellsouth.net... > .... It could be that an older table that is readable by your version of > excel is altered to become a newer version that excel can no longer read. > .... Hi Jimmy,
Neither an Integer data type nor Null support is compatible with the older DBase IV DBF format that is compatible with Excel. This is the same format that is used in FoxPro 2.6 for Windows. These features were added to the FoxPro DBF format in Visual FoxPro 3 and are only accessible in Excel via ODBC. Use a numeric data type: "Alter Table MyTable Add Column Miles N(6, 0)" -- Show quoteHide quoteCindy Winegarden MCSD, Microsoft Most Valuable Professional ci***@cindywinegarden.com "JimmyKoolPantz" <kohl.m***@gmail.com> wrote in message news:1162585230.391617.302220@m7g2000cwm.googlegroups.com... > qy = "Alter Table " + fname + " ADD MILES INT NULL" 
Other interesting topics
Merge/Synchronize XML Files
[Regular Expression] (aaa AND bbb) OR (ccc AND ddd) don't show error windows Printer Ink Status Can reflection tell the base class of an object? Unable to remove Beep on Alt+A Deleting Values from an XML node? VB6 query format for comparing number value Prevent Date Enter before Current date Problem with Calendar Control |
|||||||||||||||||||||||
