Import tab delimited file with shifting fields (vba)

[Automattic]

I'd like to use docmd.transfertext to pull data from a series of text files into an access table. Here's my problem, each site has a semi-unique ordering of each parameter. So for example, the sixth field might be "discharge" or it might be "precipitation", but either way the "discharge" field is labeled the same way each time. With a static import specification file i'm finding that the fifth field is being loaded/labeled the same way no matter what's in that column.

I could do a decision tree of if/then statements to choose the appropriate Import Specification file, but I'm hoping there's a different way.

Example data:
http://waterdata.usgs.gov/nc/nwis/uv?cb_00060=on&cb_00045=on&format=rdb&period=1&site_no=02142000
http://waterdata.usgs.gov/nc/nwis/uv?cb_00060=on&cb_00065=on&format=rdb&period=1&site_no=02142000
http://waterdata.usgs.gov/nc/nwis/u...00045=on&format=rdb&period=1&site_no=02142000


Does this make sense?

-A

 

[Norie]

Right, well I would suggest using some other method of import.

Perhaps a file I/O routine like dk posted.

I actually wrote a little bit of code in Excel that imports the data and appends the parameter name (eg Discharge, Precipitation) to the existing header.

Don't know if that would be any use though.

 

[boblarson]

Right, well I would suggest using some other method of import.

Perhaps a file I/O routine like dk posted.

I actually wrote a little bit of code in Excel that imports the data and appends the parameter name (eg Discharge, Precipitation) to the existing header.

Don't know if that would be any use though.

I was just thinking it would be simple to have it open the Excel file, and reorder the columns and save and close it. Then import.

 

[Automattic]

I was just thinking it would be simple to have it open the Excel file, and reorder the columns and save and close it. Then import.

It seems like massaging it in excel may be the most flexible option. I was just hoping to be able to go from raw data imput, to data adjustment, through to querried output all in access.

 

[Norie]

I can post what I've got so far.

It's pretty rough though.

Basically it uses a web query to get the data.

Then it looks for the little table of parameters and extracts the codes for the parameters in the table.

It then checks the headers of the data for the codes and appends the appropriate desctription.

Does that make any sense at all?

It's hard to explain and I honestly don't know if it helps at all.

The next step I was going to do was combine all the data moving columns, data as required.

I don't have the code right now but I uploaded the file so I can post a link to it later.

By the way, I only did it in Excel because it was the only thing handy at the time.

It could probably be done elsewhere, bit more work though.

 

[Automattic]

Private Sub cmdquery_Click()
'Variables to refer to Excel and Objects
Dim MySheetPath As String
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
 
' Tell it location of actual Excel file
MySheetPath = "c:\data.xlsx"
 
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
 
'Make sure excel is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True
 
'Define the sheet in the Workbook as XlSheet
Set XlSheet = XlBook.Worksheets(1)
 
'Insert Row and the Value in the excel sheet starting at specified cell
XlSheet.Rows(2).EntireRow.Delete


DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "temp01", "c:\data.xlsx", True
'Clean up and end with worksheet visible on the screen
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

End Sub

I modified this from stu999's code:

http://www.mrexcel.com/forum/showthread.php?t=453613

With this approach I can just paste the raw data into excel.

Much thanks to Norie, dk, and bob for help on this topic. Norie thanks for the extra bit of side work.

-A

 

[Norie]

So did you find a solution to the field name problem?

 

[Automattic]

essentially yes:

if i stuck with the docmd.transfertext pathway i would need to design an upload template for each table (i ended up running into some road blocks here too), and build a new one when a site was added. By filtering it through excel access was able to pick up on the first row as column headings, something that it wasn't doing with the transfertext option, even though column headings was set as true. Now i can throw each data file into a temporary table for each field site, and then pull the data from there.

The "XlSheet.Rows(2).EntireRow.Delete" line also cleans up that second line of identifiers in the raw data, which was something else i wanted to do with the data.

 

[Automattic]

The final solution I used for this renames and reorders the columns before importing it into an access table. Since the column headings are on the same row, and have the same name, i used an active cell offset loop and paired it with a series of if statements. It works something like:

dim i as integer
i = 0 
xl.activesheet.range("A1").activate

do until xl.activecell.value = ""
i = i + 1

  if xl.activecell.value = "x" then 
  xl.activecell.value = "y"
  end if

  if xl.activecell.value = "a" then
  xl.activecell.value = "b"
  end if

xl.activecell.offset(0,1).select

loop

once the column headings were there I ran a second loop to put them into a different order:

xl.activesheet.range("A1").activate
dim j as integer
j = 0

do until xl.activecell.value = ""
  j = j + 1
  
  if xl.activecell.value = "b" then
  xl.columns(j).copy
  xl.columns(i + 1).pastespecial (xlvalues)
  end if

  if xl.activecell.value = "y" then
  xl.columns(j).copy
  xl.columns(i + 2).pastespecial (xlvalues)
  end if
loop

This put them in the proper order, but left a whole bunch of extra columns, which i cleaned up by using something like:

dim k as integer
k = i

do until k = 0

xl.activesheet.columns(k).entirecolumn.delete
k = k - 1

Loop

I hope someone finds this useful, even if it is messy.

-A