Manipulating data in random fields

[Lobster1071]

I’m trying to write a program to do the simplest task, but of course I am stuck on how to accomplish this.

The program is going to do this…

1) Import an Excel file
2) Remove trailing blanks from all fields
3) Export it as a DBF file

Sounds unbelievably easy, right? I can do steps 1 and 3 with no problem of course. It’s step 2 that I need a little advice.

You see, I never know the format of the Excel file that I will be importing. I never know what fields I’m getting. If the field names were fixed, it would be no problem.

Obviously I need to use the RTrim function once it's imported, but it’s the non-constant field names that I am having a problem with. Would be nice to use something like a “Scatter Memvar” from FoxPro, but I know there’s no equivalent function in Access.

Also, I would think I would need to use some function to check that the field that I am removing the blanks from is a Text field. I couldn’t of course remove trailing blanks from a Numeric or Date field if it happens to import from Excel that way.

Can anyone possibly point me in the right direction?


Thanks.

 

[Parimal (cola)]

Hi i am doing a database that will involve bank details and card details so can any one help me with and it involves transaction details

 

[DCrake]

Firstly, don't like the font.

If you can accomplish task one then Access attempts to detemine the type of field that is being imported. Are you importing it into a new table each time or are you importing it into an existing table?

Also to state the obvious why are you not opeining the original file in Excel as saving it in DBF format?

David

 

[Lobster1071]

Are you importing it into a new table each time or are you importing it into an existing table?
David

I would think it would have to be a new table every time. I have no idea how many fields, or what their names are for each Excel file. The only thing I can keep consistant is the table name that I am importing the random Excel file into by using the TransferSpreadsheet command. I of course created a very simple form and command button to run the code:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Imported Excel File", Me.Import_Field, True

Also to state the obvious why are you not opeining the original file in Excel as saving it in DBF format?

David

I can do that fine, but if the computer this is being run on happens to be using Office 2007, you can't save an Excel file as a DBF (one of the many features that I bang my head against the wall about). Plus, the people I am writing this for requested that it be as simple as possible and just import an Excel file, clean it up, then split it out as a DBF.

The whole problem I'm having is just telling Access to run the "RTrim" command on all the fields in a table. I just don't know the field names, or number of fields each time I import an Excel file.

If I can do some kind of coding that will:
Count the number of fields in a table
Create a "For" statement (ie. For X= 1 to NumOfFields)
Then have a statement like RTrim(field(X))

I know that's basic, but that's what I am looking for, I just don't know the commands I would need.

 

[DCrake]

To get the number of fields in a table us the following

Public Function FieldCount(TableName As String) As Integer

Dim Rs As DAO.Recordset

Set Rs = CurrentDb.OpenRecordset(TableName)

FieldCount = Rs.Fields.Count -1

Rs.Close

Set Rs = Nothing

 

[The_Doc_Man]

First, Parimal, start a new thread. Don't try to hijack someone else's thread. Bad manners to do so.

Second, when faced with the problem of not knowing field names, you can examine the fields of the recordset as a collection. To do this right, you must first read up on the topic of Collections, which is in Access Help. They have examples for elaborating members of a collection. You are on the right track with the ... Fields syntax.

Of course, an RTRIM is useless unless the field is a text type, so you might wish to look into the Object Browser to get the correct names for data types, which is in (I think) the Access library, object "Type" or "DataType" - you'll know it when you see it because it enumerates the various database types. Then you can do an IF statement to test that the type is appropriate for RTRIM. Otherwise you'll get ugly results.