I used the Excel CSV method to import some data into R:Base, and found one problem. If the Excel data does not exactly fit each column -- if something is longer than the column width, spills into, and is partially hidden under the adjacent column -- you get some pretty weird results when importing the CSV-formattted data.
This was a real headache the first time I brought Excel data into R:Base. Because of this, I always opened the spreadsheet in Excel and made sure that the column widths matched the actual data width, adjusting the Excel columns as necessary. Once I did this, the Excel CSV data came into R:Base fairly successfully, and didn't require much cleanup. The temporary table idea, which I didn't use, probably would have been good because any cleanup could be done there rather than in the bigger main table. But, IMO, a key is to make sure all of the Excel data fits its columns there before starting the import process. Dick Croy suredata wrote: > Fred, > > My preferred method is to save the Excel spreadsheet as a CSV file, and load > it into an R:Base table. I used this method extensively while working on a > recent project with large amounts of data coming in from various sources. > If both your table structure and the spreadsheet layout are fixed, then you > could write a simple app to automate the R:Base end of the process. If the > incoming data needs to be cleaned or validated, then you might want to load > the CSV file into a temporary table first and do what you need to do there. > The final step involves appending the temporary table with cleaned data to > the permanent R:base table. > > Stan Loo > > ----- Original Message ----- > From: "Fred Antrobus" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Sunday, January 06, 2002 6:46 PM > Subject: Working with Excel and R:Base > > > I am about to work on a new project > > > > A office uses excel to keep track of each employees time > > > > I want to be able to take these spreadsheet forms once a month and import > > the data into the > > R:Base billing system that they have been using for years. > > > > There will be 15-30 sheets every months sent via email to the time and > > billing person > > Because of training issues (something about old dogs and new tricks...) > > I do not want to make a R:Base entry form for the end user. > > > > I was looking for suggestions on the best approach > > > > The two obvious ones are: > > 1. Do a gateway import into a temp table > > 2. ODBC connection selecting data into a temp table > > > > Any suggestion or comments are welcome. > > > > Thanks, > > Fred Antrobus > > [EMAIL PROTECTED] > > (425) 235-7796 > > > > > > > > ================================================ > TO SEE MESSAGE POSTING GUIDELINES: > Send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: INTRO rbase-l > ================================================ > TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] > In the message body, put just two words: UNSUBSCRIBE rbase-l -- Richard S. Croy [EMAIL PROTECTED] ================================================ TO SEE MESSAGE POSTING GUIDELINES: Send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: INTRO rbase-l ================================================ TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED] In the message body, put just two words: UNSUBSCRIBE rbase-l
