Split Fields During Raw Data Import

[rawlstonb]

I'm attempting to automate the importation of excel docs into a database... I've got it to where the database is taking two separate raw data pulls... creating temp tables from that... and sifting through the needed data and discarding the unneeded columns through two separate append queries... (haven't made it past the select queries to append them yet, because I can't get it to pull the data in the proper format yet)

I'm not super happy with the existing Excel doc structure, but if I edit them at all (without spaces, special characters, delete columns, etc), someone will have to manually do that before each data pool... I like the idea of creating tmptblX, tmptblY, and append querying new records, but I hit a couple snags...


First Problem:

Two fields I would like to split are joined under one column in the raw data... I've tried to run the query with "VendorID: Left([Vendor/supplying plant],7)" in "Field:", but it either takes an inordinate amount of time to run or won't finish (I haven't waited around long enough to find out)... and I'm still looking for an answer on how to handle pulling all of the field minus the first 8 characters in the next column...

So e.g.

tmptblImportData:
[Vendor/supplying plant]
1001011 DURAPART MACHINE, LLC

tblMain Data:
[VendorID] [VendorName]
1001011 DURAPART MACHINE, LLC



There are thousands of records that will be updated monthly, so it needs to be fairly clean in implementation


Second problem:

There will be several tables that are imported each update... they will name themselves "tmptbl"*"Data" and "tmptbl"*"OTD... is there a way to set my query up to run on "variable" tables where they meet that criteria or do I need to import them one at a time and change the spreadsheet name to a static value each import for the query to work correctly?... Obviously the ladder is not preferable...

... and if I can, how to delete all the tmp tables after the queries do their thing

[June7]

No, query cannot run on "variable" tables. This would require VBA to dynamically build action SQL statements that can be run within VBA. I use: CurrentDb.Execute "SQL statement here"

Why don't you import into permanent "temp" table (table is permanent but data is temporary)? This is preferable to repeatedly creating and deleting table objects which changes db structure.

Import data without splitting, then split when inserting to master table.

Use Mid function to drop the first 8 (includes space) characters.

Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

Do you have a master table of vendors? Should not repeat the vendor name in related data tables.

[rawlstonb]

I don't have the luxury of renaming fields, as that's predetermined by a separate database unless I want to create more work for the person importing the data.

the permanent tmptbl is an option.

the imported table is the raw data, nothing done to it... it doesn't want to split the data when I try the select query I posted... haven't made it to append yet

[rawlstonb]

Fixed the first problem... thanks for the Mid recommendation... I'm not sure what was crashing the original query but it ran fine after I rebuilt it?

Just now starting to look into the second issue...

I'll have separate import buttons to feed tblMain and tblOTD as both data pulls will have a different structure


The import button is currently set up as

Private Sub btnImport_Click()Dim blnHasFieldNames As Boolean
 Dim strWorksheet As String, strTable As String
 Dim strPath As String, strPathFile As String


' Change this next line to True if the first row in EXCEL worksheet
 ' has field names
 blnHasFieldNames = True


' Replace C:\Documents\ with the real path to the folder that
 ' contains the EXCEL files
 strPath = "M:\Server\KPI\ImportDocs\"


' Replace worksheetname with the real name of the worksheet that is to be
 ' imported from each file
 strWorksheet = "Sheet1"


' Import the data from each workbook file in the folder
 strFile = Dir(strPath & "*.xlsx")
 Do While Len(strFile) > 0
       strPathFile = strPath & strFile
       strTable = "tmptbl_" & Left(strFile, InStrRev(strFile, ".xlsx") - 1)


       DoCmd.TransferSpreadsheet acImport, _
             acSpreadsheetTypeExcel9, strTable, strPathFile, _
             blnHasFieldNames, strWorksheet & "$"


      ' Uncomment out the next code step if you want to delete the
       ' EXCEL file after it's been imported
       ' Kill strPathFile


       strFile = Dir()
 Loop


End Sub

So instead of "strTable = "tmptbl_" & Left(strFile, InStrRev(strFile, ".xlsx") - 1)" I would just do "strTable = "tmptblData"" and "strTable = "tmptblOTD"" to put the data on static temp tables for the two seperate buttons and change strPath to two corresponding subfolders?

Afterward, would just need something to clear tmptblData and tmptblOTD after the append query has run

Open to any other suggestions so long as they make sense to me

[Micron]

will the numeric part always be 8 characters and numeric? If not always 8 but is always numeric, maybe
strVendor = Trim(Val([Vendor/supplying plant]))
and
strVendorName =Replace([Vendor/supplying plant],strVendor,"")

As for the rest, not sure I'm grasping it fully. You mention queries but I don't see any query calls in the code, so I'm wondering what they're supposed to do and what they're querying. I agree that repeatedly creating/destroying tables is not recommended. If the source name is volatile, get the necessary name from a sheet cell or sheet name after import? Get it from the Description property of an ImportExportSpecification?

Or what you might be able to make use of is a temporary TableDef or QueryDef, which only exists for the life of the procedure it's created in. Note: I'm not referring to a temp table. I've used temp qdf before, but not a temp tdf so I can't swear that you can query one in the normal fashion. For sure you would be able to copy and manipulate its recordset. It might be hard to find a lot of info on temp TableDef as most of your browser returns will probably be about temporary tables. If interested, what to look for is the method of creation, sort of like (for temp QueryDef)

Set tmpQdef = CurrentDb.CreateQueryDef("", rest of parameters go here)

By not giving it a name ("" part), as mentioned, it's gone when the procedure ends - at least that's been my experience due to having the object variable at the procedure level. Have never tried declaring the object variable to have a wider scope, but that ought to be possible too.

[rawlstonb]

I haven't added the query to that, that code was just pulling the raw data tables from the excel spreadsheets

BUT... I prematurely celebrated the query problem being done...

So if I just do a query of:

VendorID: Left([tmptbl_1108 data].[Vendor/supplying plant],6)
VendorName: Mid([tmptbl_1108 data].[Vendor/supplying plant],11,40)

it outputs the split field how I want... splitting the raw data cell properly...

the VendorID will always be 6 digits... the raw data puts a bunch of spaces between, then the vendor name starts at character 11 and never exceeds 40 characters... works fine...

....... but when I add in the other data that needs to be pulled in the "data format for tblMain" query... it just runs open-endedly, never giving a result...

So I tried to do the "split field" query separate, and it worked fine again... but I have nothing to link to in the "data format for tblMain" query... so I thought I'd just add the key ID to it but when it imports there IS NO key ID ... and even if there WAS a key ID in the imported table, if I add any other thing to the "split field" query, I get a "Your query does not include the specified expression 'Left([tmptbl_1108 data].[Vendor/supplying plant],6)' as part of an aggregate function." prompt...

So... I'm just disgruntled at this point and ranting about Facebook whoring themselves out for Christmas every 3 posts on my feed with flying Clownfish ATM... here's some pics... of what I've got...

I'm starting to think maybe the vendors should just have their own table... then on import, lookup the vendor with "VendorID: Left([tmptbl_1108 data].[Vendor/supplying plant],6)" as criteria? and pull the matching VendorName from that table?

I'm not good at this and I keep thinking I can do things and figure it out then I get a light of hope and the jenga tower crashes down

Attachment 36494

[ssanfu]

Any chance you would post your dB and the Excel data file?

Do a "Compact & Repair", then Zip (compress) the dB. Add the Excel workbook to the zipped (compressed file)

[June7]

Very Odd. I've never had issue using string manipulation functions in any SQL.

Interesting that Micron mentioned temporary QueryDef - I recently encountered something like this in another discussion https://www.utteraccess.com/forum/in...pic=2051817&hl=. During research, found the following https://docs.microsoft.com/en-us/off...def-method-dao

I did suggest in post 2 that vendors should have their own 'master' table.

If you get the split values out to table, it is possible to then in another query (or in table Calculated field) concatenate those fields back to the original structure and use that as link to the original data.

My point is, there are several ways to get around the split issue, but probably the ideal is to figure out why the splitting is causing issue.

[rawlstonb]

Sorry, been super busy and haven't had time to mess with it... Here it is if you guys want to play with it...

Just a rundown...

qryDataImport was where the data split problem was...
qryOTDStats calculates the counts of 1's and 0's that determines whether the delivery was "on time"
qryOTDPercentage puts those values into a Percentage stat
qrySearch is tied to frmSearch so you can search by DIN/Vendor/Plant
frmImport needs work for the "temp tables"
rptDinSearch is tied to frmSearch to output the needed data

The excel sheets are how a raw data pull is going to be structured without any editing

I'm not married to anything on how it's structured, but the raw data pull needs to not require editing to pull the data into tblMain and tblOTD

[June7]

qryDataImport includes table tmptbl_1108 which does not exist. There are no expressions to split values. The VendorNumber has 6-digit prefix, not 7. There is one that begins with a V - V11271. Those digits are followed by 5 spaces then the vendor name text.

I have no problem building query splitting the value and including other fields.

SELECT tblMain.*, Left([VendorNumber],6) AS VendNum, Mid([VendorNumber],12) AS VendName FROM tblMain;

These functions are even available to table Calculated field.

Since your db does not include the failed query, cannot determine why it did.

[rawlstonb]

I had to delete the "temp" tables to get the file size under 2MB... if you change the import VBA to whatever directory you're putting the import files and click the import button, they'll pull in

[June7]

Okay, imported but had to rename table to agree with name already referenced in query - the word 'raw' is not in the reference.

qryDataImport has two tables but because there is no JOIN clause this is a Cartesian relationship - every record of each table associates with every record of other table and you are using GROUP BY as well. This query is not practical - basically crashes Access. Some of the worksheet fields have not been pulled into the query grid - why?

There is some inconsistency in the vender ID. One is only 4 digits (1150) and several with 9 characters (V11001108). The Left() function alone does not accommodate this.

Left([Vendor/supplying plant], InStr([Vendor/Supplying plant]," ")-1)

Then to pull name part: Trim(Mid([Vendor/supplying plant], InStr([Vendor/Supplying plant]," ")))

Remove tblMain from the query. Use GROUP BY under the two splitting fields and the query runs fine.

[rawlstonb]

The columns in tblMain are all that's necessary for the scope of the database.

Also understand... the spreadsheet is a raw data pull from the server that will have some superficial entry errors (there's no 1150 or non-6-digit Vendor IDs in reality)

tblMain is in the query to append the records to once the select query is functioning properly

So I've got this...

SELECT DISTINCTROW [tmptbl_1108 data].[Document Date], [tmptbl_1108 data].Plant, [tmptbl_1108 data].[Purchasing Document], [tmptbl_1108 data].Item, Left([Vendor/supplying plant],6) AS VendID, Mid([Vendor/supplying plant],12) AS VendName, [tmptbl_1108 data].[Short Text], [tmptbl_1108 data].[Material Group]
FROM [tmptbl_1108 data], tblVendor INNER JOIN tblMain ON tblVendor.VendID = tblMain.VendID;

... and that appears to output correctly, though it takes some time to calculate, so I wonder if I'm not still doing something wrong there...

[rawlstonb]

I was planning on doing a running tblVendor... which was easy enough to make... so the query that split apart the Vendors and VendorIDs and updated tblMain, I was gonna run off of to update new vendors in tblVendor...

SELECT qryRawData.VendID, qryRawData.VendName
FROM qryRawData INNER JOIN tblVendor ON qryRawData.[VendID] = tblVendor.[VendID]
WHERE (((tblVendor.VendID) Is Null));

So I purposefully deleted a few Vendors that were on there to test it... but I get no results on running it

[June7]

That query is neither updating nor inserting. INNER JOIN requires related records in both tables to exist. Change the JOIN type.