If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Import Table Structure from Excel
I have been a long time user of Borland Visual Dbase (because of its
convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#2
|
|||
|
|||
Import Table Structure from Excel
Creating a well-normalized relational database table (e.g., in Access) "with
a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#3
|
|||
|
|||
Import Table Structure from Excel
Jeff,
Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#4
|
|||
|
|||
Import Table Structure from Excel
No. Access limits the number of columns to 255 (a theoretical limit).
As for why "the finer points of database design" matter, in Access at least... You will find that Access' features and functions work better (i.e., with less work on your part) if you feed it normalized data, not spreadsheet-like layouts of "a few hundred" columns. If you haven't already, considering following the link to Duane's work -- it could really save you a lot of headache, if you are determined to go forward with using Access. Just because you could do it in Dbase doesn't make it desirable or a good idea in Access... Could you just use Dbase? -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue .... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#5
|
|||
|
|||
Import Table Structure from Excel
As Jeff says, the maximum number of fields in a JET table is 255, so if any of your tables contain more fields than that you will not be able to import them into a single JET table. If none of your tables exceed 255 fields, then you could continue to use dBase to create the tables from your Excel files, and your Access application could import or link the resulting dBase tables. If you can find a way to create an XML Schema from your Excel specifications, you might be able to use that XML Schema to create the tables. I can't say for sure, as I have not made much use of the XML features in recent versions of Access, but I think that should be possible. Other than that, you would need to write code to read the specification from the Excel files and create the JET tables. Provided the Excel files stick to a standardized format and layout, it would not be particularly difficult to do so. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#6
|
|||
|
|||
Import Table Structure from Excel
Thanks to Jeff and Brendan. I can tell you are trying to help but I think the
truth is that I'm not communicating well enough what I want to do. As a consequence, you are not responding to the question I'm trying to ask. By the by, the 255 columns thing is exactly the same in Dbase. I circumvent this by breaking the data into two or more tables. Here's WHY I'm asking the question - Borland no longer supports Dbase as a product and, with operating system changes etc., it's only a matter of time before I'll be unable to run Dbase on a PC (unless I keep a legacy system just for the purpose). That means I MUST think about migrating to a different piece of database software. (My first thought was just to use Excel but its data import facilities are primitve, requiring maximum manual manipulation and mouse clicking). It also means that, yes, I can continue to use Dbase in the short term but not in the longer term. Here's WHAT I need to achieve. I need to get the data from surveys - over which I have no editorial or technical control - into tables. The data arrives in text files with a data map, as I've said. Here's what I DO when I have survey data to load. The data map (spreadsheet) lists the variable names, column number in the record, and column width. I rapidly convert this (with a few simple Excel formulae) to a data table structure(s), treating every field as text. Each structure is saved from Excel as a Dbase table. I then use Dbase's CREATE USING command to create a new table from the structure table. Finally, I populate the new table with Dbase's APPEND FROM command. Here is my criterion for a satisfactory solution. I need to get from raw data to populated data tables in the shortest time and with the least possible effort, given that every survey is unique in its structure. As I've said, this normally takes me less than half an hour with Dbase. I have to do very little typing and most of the mouse operations involve holding the left button down and using the scroll wheel. My suspicion is that Access's data import facilties are just as primitive as those of Excel and that it's facilities for table definition/construction are equally primitive, albeit that they are wrapped up in fancy clothes. That, at least, is my perception based on my attempts so far to use Access to do this seemingly simple task. I don't doubt that I COULD use VBA or, if I had 2003 stuff, .NET programming to do the job - I can certainly write the programs - but two things mitigate against this. First, every situation I have to deal with is uniquely structured and would have to be rewritten each time. Second, everything I've ever done with VBA and .NET has been like pulling teeth once you stray from the fine line laid down in the help files. The simple truth is that I would rather not go this route unless I have to. I don't know whether this is any clearer. Or is it that I just need to read your answers so far as "No, there is no easy way to do this simple task"? Regards, "Brendan Reynolds" wrote: As Jeff says, the maximum number of fields in a JET table is 255, so if any of your tables contain more fields than that you will not be able to import them into a single JET table. If none of your tables exceed 255 fields, then you could continue to use dBase to create the tables from your Excel files, and your Access application could import or link the resulting dBase tables. If you can find a way to create an XML Schema from your Excel specifications, you might be able to use that XML Schema to create the tables. I can't say for sure, as I have not made much use of the XML features in recent versions of Access, but I think that should be possible. Other than that, you would need to write code to read the specification from the Excel files and create the JET tables. Provided the Excel files stick to a standardized format and layout, it would not be particularly difficult to do so. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#7
|
|||
|
|||
Import Table Structure from Excel
Yes you can continue to use your version of dbase in the long term.
Microsoft has a nifty tool named Virtual PC. The current version runs on Windows XP. There are earlier versions out in the wide world that run on earlier OSs. Right now VPC costs $129 for a single license. I believe that it will be an included part of Windows Vista. With VPC you can create a virtual PC running an earlier OS. It's up to you to install the OS and maintain and manage it just as you would a "real" system. If your need is only to support your own version of the application, VPC is the way to to. I doubt that you can migrate your app to a current platform for less than the cost of VPC. It can be a bit of a pain to get started with VPC but, once you've mastered the process of creating a new OS image, it's incredibly useful. HTH -- -Larry- -- "Les H" wrote in message ... Thanks to Jeff and Brendan. I can tell you are trying to help but I think the truth is that I'm not communicating well enough what I want to do. As a consequence, you are not responding to the question I'm trying to ask. By the by, the 255 columns thing is exactly the same in Dbase. I circumvent this by breaking the data into two or more tables. Here's WHY I'm asking the question - Borland no longer supports Dbase as a product and, with operating system changes etc., it's only a matter of time before I'll be unable to run Dbase on a PC (unless I keep a legacy system just for the purpose). That means I MUST think about migrating to a different piece of database software. (My first thought was just to use Excel but its data import facilities are primitve, requiring maximum manual manipulation and mouse clicking). It also means that, yes, I can continue to use Dbase in the short term but not in the longer term. Here's WHAT I need to achieve. I need to get the data from surveys - over which I have no editorial or technical control - into tables. The data arrives in text files with a data map, as I've said. Here's what I DO when I have survey data to load. The data map (spreadsheet) lists the variable names, column number in the record, and column width. I rapidly convert this (with a few simple Excel formulae) to a data table structure(s), treating every field as text. Each structure is saved from Excel as a Dbase table. I then use Dbase's CREATE USING command to create a new table from the structure table. Finally, I populate the new table with Dbase's APPEND FROM command. Here is my criterion for a satisfactory solution. I need to get from raw data to populated data tables in the shortest time and with the least possible effort, given that every survey is unique in its structure. As I've said, this normally takes me less than half an hour with Dbase. I have to do very little typing and most of the mouse operations involve holding the left button down and using the scroll wheel. My suspicion is that Access's data import facilties are just as primitive as those of Excel and that it's facilities for table definition/construction are equally primitive, albeit that they are wrapped up in fancy clothes. That, at least, is my perception based on my attempts so far to use Access to do this seemingly simple task. I don't doubt that I COULD use VBA or, if I had 2003 stuff, .NET programming to do the job - I can certainly write the programs - but two things mitigate against this. First, every situation I have to deal with is uniquely structured and would have to be rewritten each time. Second, everything I've ever done with VBA and .NET has been like pulling teeth once you stray from the fine line laid down in the help files. The simple truth is that I would rather not go this route unless I have to. I don't know whether this is any clearer. Or is it that I just need to read your answers so far as "No, there is no easy way to do this simple task"? Regards, "Brendan Reynolds" wrote: As Jeff says, the maximum number of fields in a JET table is 255, so if any of your tables contain more fields than that you will not be able to import them into a single JET table. If none of your tables exceed 255 fields, then you could continue to use dBase to create the tables from your Excel files, and your Access application could import or link the resulting dBase tables. If you can find a way to create an XML Schema from your Excel specifications, you might be able to use that XML Schema to create the tables. I can't say for sure, as I have not made much use of the XML features in recent versions of Access, but I think that should be possible. Other than that, you would need to write code to read the specification from the Excel files and create the JET tables. Provided the Excel files stick to a standardized format and layout, it would not be particularly difficult to do so. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#8
|
|||
|
|||
Import Table Structure from Excel
Oops! I forgot to mention that if you're a current subscriber to
TechNet you already have it. HTH -- -Larry- -- "Les H" wrote in message ... Thanks to Jeff and Brendan. I can tell you are trying to help but I think the truth is that I'm not communicating well enough what I want to do. As a consequence, you are not responding to the question I'm trying to ask. By the by, the 255 columns thing is exactly the same in Dbase. I circumvent this by breaking the data into two or more tables. Here's WHY I'm asking the question - Borland no longer supports Dbase as a product and, with operating system changes etc., it's only a matter of time before I'll be unable to run Dbase on a PC (unless I keep a legacy system just for the purpose). That means I MUST think about migrating to a different piece of database software. (My first thought was just to use Excel but its data import facilities are primitve, requiring maximum manual manipulation and mouse clicking). It also means that, yes, I can continue to use Dbase in the short term but not in the longer term. Here's WHAT I need to achieve. I need to get the data from surveys - over which I have no editorial or technical control - into tables. The data arrives in text files with a data map, as I've said. Here's what I DO when I have survey data to load. The data map (spreadsheet) lists the variable names, column number in the record, and column width. I rapidly convert this (with a few simple Excel formulae) to a data table structure(s), treating every field as text. Each structure is saved from Excel as a Dbase table. I then use Dbase's CREATE USING command to create a new table from the structure table. Finally, I populate the new table with Dbase's APPEND FROM command. Here is my criterion for a satisfactory solution. I need to get from raw data to populated data tables in the shortest time and with the least possible effort, given that every survey is unique in its structure. As I've said, this normally takes me less than half an hour with Dbase. I have to do very little typing and most of the mouse operations involve holding the left button down and using the scroll wheel. My suspicion is that Access's data import facilties are just as primitive as those of Excel and that it's facilities for table definition/construction are equally primitive, albeit that they are wrapped up in fancy clothes. That, at least, is my perception based on my attempts so far to use Access to do this seemingly simple task. I don't doubt that I COULD use VBA or, if I had 2003 stuff, .NET programming to do the job - I can certainly write the programs - but two things mitigate against this. First, every situation I have to deal with is uniquely structured and would have to be rewritten each time. Second, everything I've ever done with VBA and .NET has been like pulling teeth once you stray from the fine line laid down in the help files. The simple truth is that I would rather not go this route unless I have to. I don't know whether this is any clearer. Or is it that I just need to read your answers so far as "No, there is no easy way to do this simple task"? Regards, "Brendan Reynolds" wrote: As Jeff says, the maximum number of fields in a JET table is 255, so if any of your tables contain more fields than that you will not be able to import them into a single JET table. If none of your tables exceed 255 fields, then you could continue to use dBase to create the tables from your Excel files, and your Access application could import or link the resulting dBase tables. If you can find a way to create an XML Schema from your Excel specifications, you might be able to use that XML Schema to create the tables. I can't say for sure, as I have not made much use of the XML features in recent versions of Access, but I think that should be possible. Other than that, you would need to write code to read the specification from the Excel files and create the JET tables. Provided the Excel files stick to a standardized format and layout, it would not be particularly difficult to do so. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#9
|
|||
|
|||
Import Table Structure from Excel
I made a dummy file with a string of characters with date, text and number formats and commas and no spaces. The world has moved on. It is still moving. I am using Access 2007 Beta (free). 255 columns no longer applies. Access normally accepts text data in two standard formats. The first is Variable length fields with a common delimiter value, usually a comma. This is the standard CSV or comma seperated variable format, although the comma can be replaced with other delimiter characters. The other format is fixed width columns with a space separating the columns. My data was input as fixed width with no separating characters. i.e. non-standard. In the next screen Access asked me where to put the divisions between fields,and I could put them anywhere, just point and click. I could recover errors. I could name each field I could specify its data type, including Text, Date, Number, currency, OLE object, memo, hyperlink. Actually Access guessed all the ones I had correctly. I could specify to index on that field. I could specify to ignore bits of the data I will not go into all of the options available if i pressed the advanced button. Access then asked me if I wanted to specify a key, or if I wanted it to add one, or leave the table without one. The whole operation took me six minutes, mostly time taken to enter field names. You have been wasting part of your life attached to obsolete technology. "Les H" wrote in message ... Thanks to Jeff and Brendan. I can tell you are trying to help but I think the truth is that I'm not communicating well enough what I want to do. As a consequence, you are not responding to the question I'm trying to ask. By the by, the 255 columns thing is exactly the same in Dbase. I circumvent this by breaking the data into two or more tables. Here's WHY I'm asking the question - Borland no longer supports Dbase as a product and, with operating system changes etc., it's only a matter of time before I'll be unable to run Dbase on a PC (unless I keep a legacy system just for the purpose). That means I MUST think about migrating to a different piece of database software. (My first thought was just to use Excel but its data import facilities are primitve, requiring maximum manual manipulation and mouse clicking). It also means that, yes, I can continue to use Dbase in the short term but not in the longer term. Here's WHAT I need to achieve. I need to get the data from surveys - over which I have no editorial or technical control - into tables. The data arrives in text files with a data map, as I've said. Here's what I DO when I have survey data to load. The data map (spreadsheet) lists the variable names, column number in the record, and column width. I rapidly convert this (with a few simple Excel formulae) to a data table structure(s), treating every field as text. Each structure is saved from Excel as a Dbase table. I then use Dbase's CREATE USING command to create a new table from the structure table. Finally, I populate the new table with Dbase's APPEND FROM command. Here is my criterion for a satisfactory solution. I need to get from raw data to populated data tables in the shortest time and with the least possible effort, given that every survey is unique in its structure. As I've said, this normally takes me less than half an hour with Dbase. I have to do very little typing and most of the mouse operations involve holding the left button down and using the scroll wheel. My suspicion is that Access's data import facilties are just as primitive as those of Excel and that it's facilities for table definition/construction are equally primitive, albeit that they are wrapped up in fancy clothes. That, at least, is my perception based on my attempts so far to use Access to do this seemingly simple task. I don't doubt that I COULD use VBA or, if I had 2003 stuff, .NET programming to do the job - I can certainly write the programs - but two things mitigate against this. First, every situation I have to deal with is uniquely structured and would have to be rewritten each time. Second, everything I've ever done with VBA and .NET has been like pulling teeth once you stray from the fine line laid down in the help files. The simple truth is that I would rather not go this route unless I have to. I don't know whether this is any clearer. Or is it that I just need to read your answers so far as "No, there is no easy way to do this simple task"? Regards, "Brendan Reynolds" wrote: As Jeff says, the maximum number of fields in a JET table is 255, so if any of your tables contain more fields than that you will not be able to import them into a single JET table. If none of your tables exceed 255 fields, then you could continue to use dBase to create the tables from your Excel files, and your Access application could import or link the resulting dBase tables. If you can find a way to create an XML Schema from your Excel specifications, you might be able to use that XML Schema to create the tables. I can't say for sure, as I have not made much use of the XML features in recent versions of Access, but I think that should be possible. Other than that, you would need to write code to read the specification from the Excel files and create the JET tables. Provided the Excel files stick to a standardized format and layout, it would not be particularly difficult to do so. -- Brendan Reynolds Access MVP "Les H" wrote in message ... Jeff, Thanks for your response but it doesn't seem to address my question. I am not really interested in "well normalized" - all I want to do is get data, that arrives in a text file (with an Excel spreadsheet that tells me what columns are what), into an Access table quickly and efficiently. No amount of worrying over the fine details of database/table design will matter until that first simple task is achieved. With good old Dbase, this was a matter of 10-20 minutes work (and very little typing/clicking) for a few hundred columns of data. My question is whether there is a similarly simple method of getting my data into an Access table? Regards, "Jeff Boyce" wrote: Creating a well-normalized relational database table (e.g., in Access) "with a few hundred fields" is something of a contradiction in terms. A scan of this newsgroup (tablesdbdesign) will show a strong consensus that a well-normalized table will only rarely have more than 30 fields. The fact that the survey was originally conducted using Excel is a clue ... Take a look at work that Duane H. has done on building well-normalized survey databases. Perhaps there's a way to "parse" your raw data into something that Access can more fully utilize. See: Duane Hookom has a sample survey database at http://rogersaccesslibrary.com/Other...p#Hookom,Duane -- Regards Jeff Boyce Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ Microsoft Registered Partner https://partner.microsoft.com/ "Les H" wrote in message ... I have been a long time user of Borland Visual Dbase (because of its convenience and simplicity of use) and would like to migrate to Access but have found it unwieldy. I frequently receive data from surveys with a few hundred fields. The data arrives as a .dat (text) file plus a data map (Excel). It has always been an easy task to convert the data map into a table specification, which I can export as a Dbase file. Dbase has a simple CREATE USING command, which creates a new table from an existing "structure" table. My question: Is there a simple(!) way that this can be achieved in Access? I have some experience of writing macros with VBA (Excel and Powerpoint) but would prefer to avoid the hassle. Thanks. |
#10
|
|||
|
|||
Import Table Structure from Excel
"David Cox" wrote in message ... snip I am using Access 2007 Beta (free). 255 columns no longer applies. snip Are you sure about that, David? I had not heard of any change in that area, but I don't have anything with more than 255 columns with which to test. -- Brendan Reynolds Access MVP |
|
Thread Tools | |
Display Modes | |
|
|