473,574 Members | 2,486 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Renaming a field based on content

Hi Folks,

I have been given a CD with approx 130 .xls files (bean-counters!) that I
would like to import and merge to ONE table (tblTradeshow).

The XL files are *similarly*, but not identically structured, and the first
row does NOT contain field names.

Some (actually most) of the column names *are* the same in all of the
spreadsheets.

Some sheets have additional columns for extra data (core deposit value, case
qtys, container size, etc), none of which I *really* need, but would like to
preserve if possible.

All sheets contain several blank columns and rows. Some of the blank columns
are between colums of data that I need.

I'm attempting to rename the fields in newly created tables according to
what I can find in each "Column Heading" (which is not on the first row)
Renaming the fields (common denominator approach) is the first step in
appending them to a single table.

The problem that I am running into, I have discovered, is that you cannot
change the field names while you have the recordset open, so I have
"kludged" a solution by creating duplicate tables at run time and modifying
them... so now I have 260 seperate tables!

I have succesfully automated the import and merge procedure , using the code
below.
After this is completed, I am using a function to delete all of those 260
"temp" tables.

All of this code *does* seem to do the job ... but all of these variables,
recordsets, looping, and deletion seems to be redundant.
Can anyone suggest a better way to do this?

Or should I just leave it alone, and be happy that it works? :)

TIA, Don

=============== =============== =============== =============== ==
Private Sub cmdImportMergeX L_Click()

Dim MyDB As DAO.Database
Set MyDB = CurrentDb
Dim rst As DAO.Recordset

Dim rstFiles As DAO.Recordset
Set rstFiles = MyDB.OpenRecord set("tblFileNam es")

Dim MyDir As String
Dim MyFile As String
Dim MyPath As String
Dim FileSpec As String

Dim Pos As Integer
Dim strTableName As String
Dim blnAccepted As Boolean
Dim MySQL As String

Dim Msg As String
Dim CR As String
CR = vbCrLf

Dim OldField As String
Dim NewField As String
Dim ExistField As Field
Dim ExistList As String
Dim rstExist As DAO.Recordset

'Start by browsing for the drive/directory containing the XL files
'-----------------------------------------------------------------
MyDir = BrowseFolder("F ind the directory containing the desired files")

FileSpec = MyDir & "\*.xls"
MyPath = MyDir & "\" & Dir(FileSpec)
MyFile = Dir(FileSpec)
'This section loops thru the files in the directory one at a time
'and performs the import operations
'-----------------------------------------------------------------
Do While Len(MyFile) > 0
Pos = InStr(1, MyFile, "store") 'eg F:\Bodyshop Products\Bodypr o
coveralls store.xls
strTableName = "tbl" & StripString(Str Conv(Mid(MyFile , 1, Pos - 1),
vbProperCase))
'Result: "tblBodyproCove ralls
DoCmd.TransferS preadsheet , acSpreadsheetTy peExcel97, strTableName,
MyPath, False

'We cannot modify the table that we are importing because we
'have an open recordset, so I make a duplicate copy that I can
'mess around with.
DoCmd.CopyObjec t , strTableName & "1", acTable, strTableName

Set rstExist = MyDB.OpenRecord set("tblTradesh ow", dbOpenTable)
'Build a list of existing "tblTradesh ow" fields.
'This code needs to be placed here so that this list
'gets refreshed between each table import operation.
'----------------------------------------------------------
With rstExist
For Each ExistField In rstExist.Fields
If Len(ExistList) > 0 Then
ExistList = ExistList & ", " & ExistField.Name
Else
ExistList = ExistField.Name
End If
Next ExistField
.Close
End With

'This section deals with the problem of naming the imported
'table fields. Unfortunately, the first row of the spreadsheet
'did not contain field names, so we have to hunt them down...
'----------------------------------------------------------
Set rst = MyDB.OpenRecord set(strTableNam e, dbOpenDynaset)
Dim fld As Field
Dim fldName As String
Dim fldList As String
fldList = ""

With rst
.MoveLast 'Populate the Recordset
.MoveFirst
'Find the row that contains the field names, by searching for
'a product "Line" ... a 3-letter code that identifies the mfr.
.FindFirst "F1 = 'Line'"
For Each fld In .Fields

OldField = fld.Name
NewField = StripString(fld .Value) 'Remove spaces and
punctuation from field name

Select Case NewField
Case "Part"
NewField = "PartNumber "
Case "" '(blank)
GoTo SkipField
Case Else

'If we find a new field name that is not already
'in tblTradeshow, we decide if we want to add it.
If InStr(1, ExistList, NewField) < 1 Then
Msg = ""
Msg = Msg & strTableName
Msg = Msg & " contains: "
Msg = Msg & NewField & CR
Msg = Msg & "where I'm expecting a field name" & CR
& CR
Msg = Msg & "Do you want to use this as the field
name? "

If MsgBox(Msg, vbYesNo) = vbYes Then
'This is where the field name actually
'gets added to tblTradeshow
MySQL = ""
MySQL = MySQL & "ALTER TABLE tblTradeshow "
MySQL = MySQL & "ADD COLUMN "
MySQL = MySQL & NewField
MySQL = MySQL & " TEXT"
MyDB.Execute (MySQL), dbFailOnError
Else
'User decides NOT to add this field.
GoTo SkipField
End If
End If
End Select

'Build a list of field names
'for the table being imported.

If Len(fldList) > 0 Then
fldList = fldList & ", " & NewField
Else
fldList = NewField
End If

'Now call a function that renames the fields
'(in the duplicate table)to the field names
'that we have found above.
Call fSetFieldName(s trTableName & "1", OldField, NewField)
SkipField:
Next fld

.Close
End With
Set rst = Nothing
'Now that we have checked / changed /added fieldnames we can transfer
'(merge) the data into a common table ("tblTradeshow" )

If Len(fldList) > 0 And InStr(1, fldList, "RegCJ") > 0 Then
'Eliminate empty ROWS
MySQL = ""
MySQL = MySQL & "INSERT INTO tblTradeshow ( "
MySQL = MySQL & fldList
MySQL = MySQL & " ) "
MySQL = MySQL & "SELECT "
MySQL = MySQL & fldList
MySQL = MySQL & " FROM ["
MySQL = MySQL & strTableName
MySQL = MySQL & "1] "
MySQL = MySQL & "WHERE (((IsNumeric([RegCJ]))=True));"
'Debug.Print MySQL
MyDB.Execute MySQL, dbFailOnError
blnAccepted = True

Else
'Debug.Print MySQL
Msg = ""
Msg = Msg & strTableName
Msg = Msg & " has no valid fieldnames, and will be skipped."
MsgBox (Msg)
blnAccepted = False
End If
'Add the imported filenames to the table
With rstFiles
.AddNew
!FilePath = "#file://" & MyPath & "#"
'This converts the filepath string to a hyperlink,
'which allows the user to open the XL file from a subform link
'for diagnostic / compatison reasons ... "Show Me!"
!Imported = blnAccepted
.Update
End With

Me.Refresh
'There are 2 subforms on this form ... both based on "tblFileNam es"
' "sbfFilesImport ed" shows successful imports
' "sbfFilesReject ed" shows failed attempts. (Import fails if no Line
Code is found.)
MyFile = Dir 'Import the next XL file in the directory.

If Len(MyFile) > 0 Then
MyPath = MyDir & "\" & MyFile
End If
Loop

Set rstFiles = Nothing
Set rstExist = Nothing
Set rst = Nothing
Set MyDB = Nothing

Msg = ""
Msg = Msg & "XL Data import completed." & CR & CR
Msg = Msg & "Would you like to delete the TEMPORARY tables?"

If MsgBox(Msg, vbYesNo, "Confirm Deletion") = vbYes Then
fDeleteTempTabl es
End If

End Sub

--
TIA,
Don
=============== ==============
Use My*****@Telus.N et for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.
=============== ==============
Nov 12 '05 #1
1 3415
Cloning recordsets would help you eliminate duplicate tabledefs.

Example
Dim db as Database
Dim rec as DAO.RecordSet
Dim strNewFieldName as String

Set db = CurrentDB()
Set rec = db.Openrecordse t(<<<YOUR TABLE HERE>>>)

With rec
.Clone
<<<FIND INFORMATION TO BASE FIELD CHANGE ON>>>
Call FieldChanger(st rNewFieldName)
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1256
by: jyoti | last post by:
Hi there, Our organization's web site requires a lot of content updates. I like to develop a browser based content management system in asp. I like to show my end users exactly live format of our web site into edit mode so that they can just go there and change the text, or text within a table, or change a link. Is there any ASP component...
6
148550
by: Steve Speirs | last post by:
Hi I'm trying to show/hide a simple piece of text and a text field on a form based on what choice is made from a drop down box. <select name="dropdown" size="1"> <option selected value="">Please make a selection</option> <option value="1">Choice 1</option> <option value="2">Choice 2</option> <option value="3">Choice 3</option>
1
378
by: Don Leverton | last post by:
Hi Folks, I have been given a CD with approx 130 .xls files (bean-counters!) that I would like to import and merge to ONE table (tblTradeshow). The XL files are *similarly*, but not identically structured, and the first row does NOT contain field names. Some (actually most) of the column names *are* the same in all of the spreadsheets.
14
4691
by: SpyderSL | last post by:
Hey, I have created an access form, in which I have a drop down with employee names. These are the steps I would like to happen: 1. A user will enter a number in FIELD A 2. The user will choose an employee name from the drop down 3. A number should calculate based on the name chosen and number entered in FIELD B.
1
3180
by: compl | last post by:
I have a database that I need to restrict the ability to edit one field based on another. The two fields are DOS (date) and Amount (currency). I only want the users to be able to edit the Amount field if the DOS is =>now()-5. Does anyone have any suggestions? Thanks, Lisa
4
13948
by: whamo | last post by:
I have the need to populate a field based on the selection in a combo box. Starting out simple. (2) tables tbl_OSE_Info and tbl_Input; tbl_OSE_Info has three fields: Key, OSE_Name and OSE_Wt tbl_Input has three fields: OSE_Job, OSE_Name, OSE_Wt I have populated tbl_OSE_Info table. I need to create a form that will store the data in...
10
7369
by: dkyadav80 | last post by:
<html> /// here what shoud be java script for: ->when script run then not display all input text field only display selection field. ->when user select other value for institute only this field display not display degree text field ->when user select other value in the selection field for degree then text field display and wise versa ...
5
3572
by: Shortstuff12345 | last post by:
I'm trying to use VBA code to disable a field based on the value of another field in a form. The code I have properly updates the enabled property of the field when it changes; however, it changes it for the form as a whole instead of a single record. My form is set as a single form. For example on record 1, field1's value is "records" and...
3
2072
by: dileshw | last post by:
I have a form where I want a third field to be autofilled by a certain text based on the content of 2 previously entered fields of data. I put this following code into an on_click event of a button. (I dont care much on how it happens, I only want it to fill) If (Me.DepCity = HAM & Me.DestCity = PEK) Then Me.DepHandlingAgent =...
0
7815
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
8258
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7833
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8118
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6481
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3756
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
1
2254
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.