Solved

having trouble imported flat text file into ACCESS using file/get external data/import......

Posted on 2004-08-25
28
970 Views
Last Modified: 2012-06-27
Hi everyone,

I would like to import a flat text file into Access.
This file is being sent to me from another department (possible from a mainframe
application).
a) First, I would like to do this manually using file/get external data/import,
(learn how to create a specification file using the advanced button),

b) Then, do the import using the docmd.tranfertext.

Right now am having problems with idea: (a)

The following url was helpful:(please see the section on fixed width imports)
http://www.its.niu.edu/its/CSupport/tipoftheweek/tip_080502.shtml

Having a problem:
When I run the import text wizard:
The screen that askes whether the file is delimited or fixed width, shows a
sample data window:

On my screen, the sample data window, shows 3 rows of questions marks, like this
??????????????????????
??????????????????????
??????????????????????

(instead of text file's data) so am unable to do the import.

My colleague, who walked me through this process, has the text data showing in the sample data screen.

I can click print-screen to send my screen shots via a word document, if that helps.....


Thanks in advance for your time,
mytfein

0
Comment
Question by:mytfein
  • 12
  • 9
  • 7
28 Comments
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11894516
Can you import the file into Excel using text import wizard? You may not have the right filters to read the file - can you open it in Notepad (or Wordpad) and read it, or is it gibberish?

Once you get the file in readable format, you can use the Text Import Wizard to create a file import spec, which you can use with TransferText as well. I suggest setting up an Access table, with all the correct fields and datatype specs to receive the data (rather than importing to a new file), because Access doesn't always guess right about fieldtypes if you let it make up the specs.

-ef
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11895256
If it is a flat text file, you have to be able to open it in WordPad or NotePad and it has to be readable. It is strange your colleague see's text on his machine. If he is looking at the same file then I would say something is broken in your machine.  Try booting to a DOS window and see if you can open the file using the DOS edit.com command in C:\Windows\System32\ subdirectory. If so, something is wrong with your windows installation.
0
 

Author Comment

by:mytfein
ID: 11895605
Hi ef,

1) can open file via notepad
2) my colleague, who can open it , works in another building, she has no idea why it works on her machine
3) went to the IT network/tech people, they are not programmers....they don't know
4) so posted this question on EE

5) what do you mean by "right filters"?
6) This is first time I am createing an import spec.
    Hope I did it right.
   Click on advanced, and mapped out the layout using the record layouts, that my colleague faxed to me. Did a "save as", and gave the import spec a name.

How do I use the import spec, when importing.....please advise?

7) Based on the phrase "once you get the file in readable format",
 I'm hoping that you want me to open the file via Excel, as a one-time-shot,  as what I really want to do at the end of the exercise, is automate the import on a daily basis. (by having the mdb run at startup, or through windows task scheduler)

If that is so I tried the following experiments, with no success:

    a) imported the text file into excel. It asked me to delimit the fields by clicking to at the beginning of the field to mark the column breaks
        1) first i removed all column breaks, because wanted to make the excel row, one long column -- that idea did not work
        2) then set all column breaks clearly, by clicking where appropriate in the data
             was unsure how to "save as", first tried .prn/space delimited, the .prn did not show up in the file window, when clicking file/get external data/import
             -so-
              went back to excel and did a "save as" to  .txt/ms-dos
    b) evoked the import wizard to import .txt/msd-dos into access:
    where do I plug in the name of the import spec?
    c) the sample data window in the import wizardnow shows data,
          however with little square boxes. I was surprised because, I want a flat text file, without delimiters.

8) Are you suggesting that on the Access side 2 things need to be done:
    a) creating an import file spec
    b) creating a table with all columns mentioned in the file spec, and where each column has the
same data type and length as the import file spec

9) can you print the import file spec?


Hi Grayl,

My colleague who sits next to me, tried the import wizard as well, and she also gets question marks......

We are both using windows 2000 and access 2000

I've never booted to dos (though open to to following a walk-thru)

So dear experts, still need assistance,
thank you, mytfein
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11895857
My suspicion is the file itself. A prn file can in fact be full of printer specific commands, are generally huge, and while they may be opened with WordPad they are not readable per se. What do you see when you open it with WordPad? If you see a lot of garbage, it is not a text file. Exit and talk to your other department and determine the source of the file.
0
 

Author Comment

by:mytfein
ID: 11896053
Hi GrayL,

I am able to see the original text file via notepad.....

When EF suggested, that I open this file via EXCEL, did not know which kind of .txt file to save as...

(I remembered that in my previous job as a mainframe COBOL programmer, I would save vendor files that were sent as CSV files to  PRN via EXCEL, for upload to the mainframe, to be processed by a COBOL program. )

Access did not show PRN in the import wizard file window.
So saved the spreadsheet as a .txt/ms-dos, file instead.....(please see my previous comments for how that fared...)

Would it help if I emailed you a screen shot of what my screen looks like with the import text wizard?

In the meantime, I have defined an import spec, and am in the middle of creating a table to hold these fields.

I'll try doing a docmd.transfertxt with the file spec etc., and see if that works.....

After that, I do not know what to do next.....
0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11896296
OK-if you can open the file in Excel using the text import wizard, it must be one ofthe types Excel can read. When you tried to import it in Access, did you set the file type to text? It's pretty far down on the pull down list at the bottom of the file picker window - it lists txt,csv,asc,tab files. You HAVE to select this item for Access to correctly invoke the text import wizard. As GrayL says - if its a prn file, Access can't handle it, but Excel can - which sounds like your situation. So either you need to get a different format from your source, or you can import the file to Excel, save it as a .xls, then import it (or link to it) in Access. You can do all of that from Access in VBA, if that's want you want. You would save your import spec in Excel, not Access; then, as I suggested, bring the data into a well-defined table in Access (just so Access doesn't have to guess about each column's datatype in Excel.)

Let me know if that's sounds OK, and I'll try to give you some VBA to get it done.

-ef
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11896454
mytfein: Mighty fine work so far! ;))
0
 

Author Comment

by:mytfein
ID: 11897330
Hi ef,

1) Right from the beginning, I selected the .txt data type in the import text wizard
    it's just that the sample data window showed '?????', instead of data

2) the .txt file is created on the mainframe, so there are embedded spaces in the file: These chunks of spaces are called FILLER, and need to be retained.

sample layout of the first 5 fields (in total there really are about 45 fields, for testing,
 I created an a) abbreviated file spec (Eagle Short, marked fields 6-45 as "skip")
             and b) abbreviated table (T_Eagle_Short)

fieldname         start    length
===================
headerInfo        1         45
patientNumber  46        7
filler                 53        8
patientName     61       26
patientStreet    87        25

sample data:
0000761040823092812CO2IC PMMN01SLEES          721036        DOE,JANE                  100 MAIN

(I just realized that the patient no is 6 characters, not 7, will call their programmer, who is gone for the day)

Anyway, when importing to EXCEL, using fixed width option:
 1)    EXCEL did not retain the separator spaces called FILLER. The spaces evaporated. I need the spaces retained.
 2)   where do you specify the import file spec in the import on the EXCEL side?
 3) save imported file as .xls, when did an import on ACCESS side, it grayed out the option to import to an existing table -- only allowed to go to a new table.

When I went back to my idea of doing a docmd.transfer text:

When I tried to do the text import via the docmd.transfer text, instead of manually thru file/get external data/mport using the following code:

Public Sub ImportEagle2()

DoCmd.TransferText acImportDelim, _
                   "Eagle Import", _
                   "T_Eagle_short", _
                   "c:\ehspmm.txt", _
                   0

Got long error msg:
that append table failed for many possible reasons, one of which are problems in data type or data size....

The import error file that was generated gives error: truncated for field: header

I checked the 5 fields they have the same data type and field size as the import file spec.

Well, it's been quite a day....maybe this cannot be done....I'm going to ask the mainframe programmert, if they can generate a comma-delimited file, maybe I'll have better luck with that.....

It's the end of day over here on the East Coast, so have a good evening y'all.....
mytfein

p.s. Hi GrayL -- yes, you intuited the underlying meaning of my screen name....cheers!



0
 
LVL 8

Accepted Solution

by:
Eric Flamm earned 125 total points
ID: 11897866
Re: Excel import spec - look at the OpenText method in Excel help - the parameter FieldInfo is defined as:

FieldInfo  Optional xlColumnDataType.  An array containing parse information for individual columns of data. The interpretation depends on the value of DataType. When the data is delimited, this argument is an array of two-element arrays, with each two-element array specifying the conversion options for a particular column. The first element is the column number (1-based), and the second element is one of the XlColumnDataType constants specifying how the column is parsed.

To save your filler, you need to treat the column as fixed width, not delimited - then you delineate the Filler field and make sure it's included in the import.

I did a fixed width import using the Macro recorder and got this:
Workbooks.OpenText Filename:= _
        "C:\Documents and Settings\Dad\My Documents\Compare Reg Keys.txt", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, _
        1), Array(18, 1), Array(38, 1), Array(56, 1)), TrailingMinusNumbers:=True

If the Access sample data window showed ???????, I would guess there are some binary characters at the top of the file (maybe printer control) that Access couldn't interpret. At least with Excel you can tell the import which Row to start with.

Access converted consecutive spaces to nulls - why do you need to preserve the filler - you can always recreate it with Space(8) or whatever.

In Access, need to use acImportFixed, not acImportDelim, unless you have a delimited file (which you don't)

Well, that's some of your questions - you should be able to do the TransferText right into Access, but you'll generate some error records from the garbage at the top of the file. I don't see any way to control which records you import as part of the import spec.

-ef
0
 

Author Comment

by:mytfein
ID: 11903168
Hi ef,

Good Morning! A new day....a fresh mind....

ok, making a little progress,

using your above suggestions:

1) "perhaps the file has BINARY characters"
    Created a import_test.txt file in note pad,
    with just 3 fields:       DOE            JANE              100 MAIN ST

2) a) opened the import wizard,
     b) selected the import_test.txt
     c) The data SHOWS up in the sample data window!
     d) clicked advanced, created a file spec

3) created a table with 3 fields, with the same data type and field size as file spec

4) you're right -- I should be using acImportFixed, and NOT acImportDelim

5) ran the following code:

Public Sub ImportEagle3()

DoCmd.TransferText acImportFixed, _
                   "Import_test_spec", _
                   "T_import_test", _
                   "c:\Import_Test.txt", _
                   0


End Sub


6) The above code worked! Data imported to appropriate columns......

So at least ACCESS seems to be installed properly on this computer......

==================================================

Now I'm going to look into your EXCEL suggestions.....

Will be in touch.....
mytfein
0
 

Author Comment

by:mytfein
ID: 11905257

Hi ef,

I found this link on the web which helped me understand your EXCEL idea:

http://www.oreilly.com/catalog/exlmacro2/chapter/ch17.pdf

I actually did the fixed width exercise that they describe:

a) create parse_text.txt containing the following records:

0-125-689
2-523-489

to import into excel as (without hyphens)
0      125         689
2      523         489

created this macro in excel:

Public Sub OpenText()

Dim arMulti     As Variant
Dim strFileName As String

strFileName = "C:\moffice\test_parse.TXT"

Workbooks.OpenText _
          Filename:=strFileName, _
          Origin:=xlWindows, _
          StartRow:=1, _
          DataType:=xlFixedWidth, _
          FieldInfo:= _
                 Array(Array(0, 2), _
                       Array(1, 9), _
                       Array(2, 2), _
                       Array(5, 9), _
                       Array(6, 2))
         


End Sub

question 1) This test worked, but what does 1 based mean, if the first field's start position is at 0? I would think, it should be 0 based.....

Also, when I tried this on the mainframe text file, just tried it with the first 3 fields of the record layout. The results were a drop skewed, it seemed that excel was ignoring the filler-field.

So I decided to call the mainframe programmer and describe this entire saga to her.

She AGREED (as you suspected) that the filler, are not necessarily blank, may contain hexadecimal characters.

 (It seems that the excel open text command is igoring the hex characters, even though I defined them in the double dimension array)

She is going to try to FTP the file again, in a way that translates the hex characters into spaces.

If/When that happens, I'l try the excel open text code again....

will be in touch....
mytfein
0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11905465
1) I think 1-based applies to delimited, not fixed width, specs - the recorded fixed width spec I gave clearly is 0-based

Guess there is no question 2... Still think you could just ignore the filler and recreate them for reporting later on if needed. You're not going to try to recreate the flat file after you've parsed it, are you?

-ef
0
 

Author Comment

by:mytfein
ID: 11905738
hi ef,

ok, the mainframe programmer's alternate  ftp idea did not work.....

so modified the EXCEL open text code to simply take the .txt record as 2 fields:

Dim arMulti     As Variant
Dim strFileName As String

'Dim myWorkbook As Object
'Set myWorkbook = New Workbook


strFileName = "C:\EHSPMM.TXT"

Workbooks.OpenText _
          Filename:=strFileName, _
          Origin:=xlWindows, _
          StartRow:=1, _
          DataType:=xlFixedWidth, _
          FieldInfo:= _
                 Array(Array(0, 2), Array(24, 2))


The excel spreadsheet looks like this:
       cell a                                                 cell b
0000761040823092812CO2IC       ###################################

If I click in row1, cell b  , I will see the actual data in the formula bar

I am assuming that #'s means that the cell is too small,
I clicked ctl_a, format, columns, auto-fit -- but that did not solve the problem.....

well, with the above 2 column excel spreadsheet, saved to a txt file (ms/dos)

went to ACCESS to do an import,

it defaulted to delimited, I guess excel put a tab delimiter between these 2 columns based on
the array instructions?

quesstion 1) any ideas, how to change the size of the cell manually and/or through vba,
 so that data, instead of #'s  display? I'm surfing the web for ideas...have not found any info yet.....

thx,
mytfein


0
 
LVL 44

Expert Comment

by:GRayL
ID: 11906570
If you grab the right vertical bar in cell b and drag it right can you get to the end of the data. It may be a few screens wide. I'm surprised auto-fit did not fix it. The column width limit of excel is 255 characters. If it is wider than that, I do not know what excel will do on an import. Maybe just what you're experiencing. When you view the file in NotePad, Click Format and make sure WordWrap is off. Each line is a record. How big are the fields?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11906699
Also, you can format the column to wrap the text for display (still 1 big field for storage) - Format, Cells, Alignment, Wrap Text - not sure how big Autofit will go (maybe just screen width), but Wrap Text will show 255 characters.

You don't need to export to text - just import the Excel file directly to Access - it likes Excel way more than text anyway.

By the way, i didn't copy enough help text last time

>If the source data has fixed-width columns, the first element in each two-element array specifies the position of the >starting character in the column (as an integer; character 0 (zero) is the first character). The second element in the two->element array specifies the parse option for the column as a number between 1 and 9, as listed in the preceding table.

>The following example parses two columns from a fixed-width text file. The first column includes characters 1 through >10. Characters 11, 12, 13, 14, and 15 are skipped. The second column includes character 16 through the last character >in the line.

>Array(Array(0, 1), Array(10, 9), Array(15, 1))

the 2d argument - 1=General (generally numbers), 2=Text, 9=Skip

So your code looks good
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11906857
I agree eflamm - I was just curious about the auto-fit thing.
0
 

Author Comment

by:mytfein
ID: 11907184
Hi everyone,

1) checked notepad: wordwrap is off
2) this is a long record layout....416 characters (about 47 fields)
3) dragging the column separator bar for cell "b" did not work
4) opened a new spreadsheet, and made cell "b" extra lengthy, before running opentext macro, did not help
5) opened a new spreadsheet, clicked on format, cell, alignment, wordwrap, before running macro, did not help....

6) emailed an mdb containg: table name, import file spec, and docmd.transfertext to the mainframe programmer, for her to show her supervisor......

if you like, i can email the same to you, and include the excel function for the opentext method...

ef, question:

I used your opentext logic as the foundation, but first I tried to get the code by setting up the
macro recorder in EXCEL , and i got totally different results:

I did:
data, import text file,      as shown way below:

What steps did you take to generate the opentext code?

====================================================
Well, I definitely learned alot, feel that I have more depth, and am really itching to apply what I've learned to a more accommodating file! Hopefully future EE readers, will benefit from this drama.......

I probably should settle this question tommorrow......

I'm going to call it a day for now.....

have a good evening!
mytfein



====================================================

Sub Macro2()
'
' Macro2 Macro
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\EHSPMM.TXT", Destination _
        :=Range("C1"))
        .Name = "EHSPMM"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = xlWindows
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(35, 8, 26, 25, 15, 12, 41, 4, 19, 14, 70, 6, 76, 89)
        .Refresh BackgroundQuery:=False
    End With
End Sub
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11907302
TextFileColumnDataTypes  -  I count 15
TextFileFixedColumnWidths  -  I count 14 ??

Something's amiss wot?
0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11907686
I did not use QueryTables - I turned on macro recorder, then I just used File Open, selected a text file at random, and picked spots to cut the lines.

You will not be able to import 477 characters into 1 column in Excel. Surely you can cut it up into smaller, menaingful pieces. If not, cut it up into smaller, meaningless pieces (say 4 x100 + 1 x77), then put them back together in a memo field in Access. Even then, you really can't "work" with a memo field, except by manually parsing it.

Do a little more work with Workbooks.OpenText - see if you can all the data more or less legibly into Excel; then you can work on moving it eficiently to Access.

-ef
0
 

Author Comment

by:mytfein
ID: 11916716
Hi experts,

I want to thank you for your help and support......
I am going to go with ef's ideas.....

=========================================
Hi ef,

1) This morning, the mainframe programmer generated the file again.
     Was able to view it successfully in Access.

2) Initially wanted to bring the data in using a docmd.transfertext, like the code which follows belows, but it did not work...and it's hard to trouble shoot it. Also, Tried a mini-file (with just 5 columns) with a mini-file spec, and i get a first row of gibberish...so put this idea aside, because was having better luck with the open text method in excel.

this is the file import tried via in access, that gave me trouble.

Public Sub ImportEagle()

DoCmd.TransferText acImportFixed, _
                   "Eagle Import", _
                   "T_Eagle", _
                   "c:\ehspmmt.txt", _
                   0
End Sub

3) In general, I understand that the above import, uses an import file spec, but if I was doing the import via the wizard, I dont see in any dialogue a place to key in the import file spec.
i do see the advanced/specs/ button to allow you to CREATE the file spec. Or can a file spec only be used thru vba.

4) Turned on the macro recorder, per your suggestions, and clicked all the columns, to generate the opentext code, instead of me having to figure out offsets by myself

5) I want to automate this process on a daily basis. The mainframe will ftp the file to a server for me to use. To that end, attempting a vb script, which is not working as seamlessly, as I had hoped.

    I have pasted the script below.
    The main problems is the very last step to transfer the spreadsheet, into a table that I have predefined getting the following error:

    run time error: 3274 external table is not in the expected format

    I did need to modify some of the excel column name, because access did not like some of my 1 letter column names.....

   but I am still getting the error:

If you have time to look, this is my script, should be able to be used, with some modification, with any text file that you already have:

'==========================
Option Compare Database

Option Explicit

Dim objExcel                     As Excel.Application
Dim objExcelActiveWkb            As Excel.Workbook
Dim objExcelActiveWs             As Excel.Worksheet
Dim blnExcelAlreadyRunning       As Boolean


Public Sub EagleUpload()

LaunchExcel

ImportTextToExcel

SaveExcelSpreadsheet

CloseExcel (True)

ImportSpreadsheetToAccess

End Sub

'=======================================
Private Sub LaunchExcel()
On Error Resume Next

'If WasExcelRunningBeforeThisExecution Then
'   blnExcelAlreadyRunning = True
'    Set objExcel = GetObject(, "Excel.Application")
'Else
    blnExcelAlreadyRunning = False
    Set objExcel = CreateObject("Excel.Application")
'End If

 objExcel.Visible = True   'False
       

'objExcel.Application.Workbooks.Add
'Set objExcelActiveWkb = objExcel.Application.ActiveWorkbook

'Set objExcelActiveWs = objExcel.ActiveSheet

End Sub

'==========================================
Function WasExcelRunningBeforeThisExecution() As Boolean

On Error Resume Next

Set objExcel = GetObject(, "Excel.Application")

WasExcelRunningBeforeThisExecution = (Err.Number = 0) ' if err.number = 0, true else false
Debug.Print Err.Number
Debug.Print Err.Description


Err.Clear

End Function

'==================================
Private Sub CloseExcel(blnHowToCloseExcel As Boolean)

On Error GoTo CloseExcel_Err


 objExcelActiveWkb.Close savechanges:=False
 If Not blnExcelAlreadyRunning Then
     objExcel.Application.Quit
 End If




CloseExcel_Exit:
    Set objExcel = Nothing
    Set objExcelActiveWkb = Nothing
    Set objExcelActiveWs = Nothing
   
    Exit Sub
   
CloseExcel_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume CloseExcel_Exit
 
   
End Sub

'====================================
Private Sub SaveExcelSpreadsheet()

On Error GoTo SaveExcelSpreadsheet_Err

   
 Const cstrPath As String = "c:\EagleEhsVisits.xls"
   
Kill cstrPath

Set objExcelActiveWkb = objExcel.Application.ActiveWorkbook
objExcelActiveWkb.SaveAs cstrPath

SaveExcelSpreadsheet_Exit:
     Exit Sub
   
SaveExcelSpreadsheet_Err:
     Select Case Err.Number
       
         Case 53     ' kill didn't find the file - ignore error
            'MsgBox Err.Number & "  " & Err.Description
            Resume Next
           
         Case Else
            MsgBox "Error # " & Err.Number & ": " & Err.Description
            Resume SaveExcelSpreadsheet_Exit
           
       End Select


End Sub

'==========================

Sub ImportTextToExcel() '

'
    ChDir "C:\"
    Workbooks.OpenText Filename:="C:\EHSPMMt.TXT", Origin:=xlWindows, StartRow _
        :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(36, 2), Array _
        (45, 2), Array(52, 1), Array(60, 2), Array(86, 2), Array(121, 2), Array(146, 2), Array(150, _
        2), Array(152, 2), Array(161, 2), Array(163, 2), Array(174, 2), Array(186, 2), Array(197, 2 _
        ), Array(207, 2), Array(208, 2), Array(209, 2), Array(210, 2), Array(212, 2), Array(214, 2) _
        , Array(221, 2), Array(222, 2), Array(230, 2), Array(240, 2), Array(247, 2), Array(248, 2), _
        Array(250, 2), Array(261, 2), Array(270, 2), Array(280, 2), Array(290, 2), Array(297, 2), _
        Array(298, 2), Array(300, 2), Array(310, 2), Array(320, 2), Array(328, 2), Array(329, 2), _
        Array(330, 2), Array(334, 2), Array(340, 2), Array(341, 2), Array(410, 2), Array(480, 2), _
        Array(481, 2), Array(499, 2), Array(519, 2), Array(520, 2), Array(521, 2), Array(522, 2), _
        Array(530, 2))
       
       
       
Range("A1").Select
    Selection.EntireRow.Insert
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "header"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "filler1"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "patientNumber"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "filler2"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "PatientName"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "PatientStreet"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "PatientCity"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "PatientCounty"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "PatientState"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "PatientZip"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "PatienCountry"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "filler3"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "PatientPhone"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "PatientSSn"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "PatientDOB"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "G1"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "M1"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "filler4"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "R1"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "Rel"
    Range("U1").Select
    ActiveCell.FormulaR1C1 = "Chart#"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "E1"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "Medicare#"
    Range("X1").Select
    ActiveCell.FormulaR1C1 = "Medicaid#"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "filler5"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "E2"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "filler6"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "filler7"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "filler8"
    Range("AD1").Select
    ActiveCell.FormulaR1C1 = "filler9"
    Range("AE1").Select
    ActiveCell.FormulaR1C1 = "filler10"
    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "filler11"
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "T1"
    Range("AH1").Select
    ActiveCell.FormulaR1C1 = "filler12"
    Range("AI1").Select
    ActiveCell.FormulaR1C1 = "filler13"
    Range("AJ1").Select
    ActiveCell.FormulaR1C1 = "filler14"
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "filler15"
    Range("AL1").Select
    ActiveCell.FormulaR1C1 = "I1"
       
       
       
  Cells.Select
  Selection.Columns.AutoFit
 
 
End Sub

Public Sub ImportSpreadsheetToAccess()


Dim strExcelFile As String
Dim strTableName As String

Dim strSql       As String

strExcelFile = "c:\EagleEhsVisits.xls"
strTableName = "T_EagleEhsVisits"
       
strSql = "DELETE FROM " & strTableName
CurrentDb.Execute (strSql)

DoCmd.TransferSpreadsheet _
      TransferType:=acImport, _
      SpreadsheetType:=8, _
      TableName:=strTableName, _
      Filename:=strExcelFile, _
      HasFieldNames:=True

End Sub





   
0
 

Author Comment

by:mytfein
ID: 11916831
Oh, another strange problem is that after the script runs, if I go to windows explorer to view the contents of EagleEhsVisits.xls, my computer just hangs

if I go to task manager, nothing is in "not responding" mode, I find that I have to exit ACCESS, in order to be able to view it.....

have I in some way used up memory, by running this script many time?

Initally i had set up excel objects, and set them to nothing at the end of processing, but I noticed that i would have to excel instances opened....

went back to help for opentext, and carefully read that, it automatically opens a new workbook etc, so that i don't have to explicitly code it in vba, which is why I  commented out.....

well, thanks again for your help....any feedback would be greatly appreciated, even though the question is now closed....

have a nice weekend,
mytfein

0
 
LVL 44

Expert Comment

by:GRayL
ID: 11917538
mytfein: You've been busy! You can call the spec from the Advanced menu - Specs.
This presumes you have created a spec already and saved it with an appropriate filename.
0
 
LVL 8

Expert Comment

by:Eric Flamm
ID: 11920701
When your computer hangs, do you see any "phantom" Excel applications - i.e., ones which are not visible but are still running? If you run the script and terminate due to an error, the objExcel object might get left open - assuming your error-handling doesn't run and kill it. If there's a phantom Excel which has your worksheet open, it might hang your PC (although usually it just opens the workbook read-only).

Since you said you ran your script "many times", this is a possibility. Access 2000 and Excel 2000 did not do the best job of memory management, so it might just be a system resources issue.

Glad to have helped,

-ef
0
 

Author Comment

by:mytfein
ID: 11931099
Hi GrayL,

You're right!

It seems that on the import text wizard,
    the advanced/specs buttons are used to
               1)  define new import file specs -and-
               2)  execute a file import based on an existing file spec,
                    selected the file spec name, clicked OK, then continued with standard text wizard button of "next", and noticed that the column break lines reflect the mappings in the file spec....

thank you!

p.s. I just have a problem updating an existing file spec, it seems that I need to click on "save as" to  save it under a new name, as I do not see an "update button"
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11932470
Can't you give it the same same? I sure you can overwrite the exisiting file.
0
 

Author Comment

by:mytfein
ID: 11933305
Hi GrayL,

thx so much! I appreciate your time!

I did not realize that the SAVE AS button on the advanced/specs screen,
      allows:
            a) creating a brand new file spec   -and-
            b) by using an existing file spec name, allows updating an existing file spec, and asks via a msgbox. if we really want to overwrite a spec, by the same name!

=================================================
The strangest thing, though, is happening:

As soon as I click, SAVE AS , after ADDING a field to an existing file spec,

I see that the field, that I just added, disappeared from the file spec....

I tried it again, hitting the tab key, many times, to get the add to stick, thinking that maybe this form, works llike a bound form, but that did not help.....

...any thoughts?
thx, mytfein
0
 
LVL 44

Expert Comment

by:GRayL
ID: 11936140
Well, it looks like the spec is not being written over. I just tested it and found I had to delete the spec before I could reuse the name. With the advanced button, click on the spec you want to update. Then click Specs again and delete the spec. Now modify the structure of the import and click Save As. You can now use the old name and the change(s) will stick.
0
 

Author Comment

by:mytfein
ID: 12048527
Hello Everyone,

A postcript:

While the script that was written for comment 08/27/04 worked....

There was a problem:
Excel was still running (invisibly -- by checking task manager/processes), though I closed it down and set the variables to nothing.....

I was struggling with this on and off, since then and was ready to give up!

Then I found a clue on the web.
One must be very careful when using excel vba generated by the macro in an access vba module. Cut and pasting the macro vba into an access module, can leave excel running invisibly.

By surfing the web, using the keyword ".openttext", found logic to see how others coded their scripts.
Went through the script to replace macro vba with  access-to-excel-automation-vba:

The revised script works (excel.exe is truly gone when the script is done) and is listed below for future readers:
mytfein

============The working code below:


Option Compare Database

Option Explicit

Dim objExcel                        As Excel.Application
Dim objExcelActiveWkb           As Excel.Workbook
Dim objExcelActiveWS            As Excel.Worksheet

Dim blnExcelAlreadyRunning       As Boolean


Public Sub EagleUpload()

LaunchExcel

ImportTextToExcel2

SaveExcelSpreadsheet

CloseExcel (True)

ImportSpreadsheetToAccess

End Sub

'=======================================
Private Sub LaunchExcel()
On Error Resume Next

If WasExcelRunningBeforeThisExecution Then
   blnExcelAlreadyRunning = True
    Set objExcel = GetObject(, "Excel.Application")
Else
    blnExcelAlreadyRunning = False
    Set objExcel = CreateObject("Excel.Application")
End If

 objExcel.Visible = True   'False
       
End Sub

'==========================================
Public Function WasExcelRunningBeforeThisExecution() As Boolean

On Error Resume Next

Set objExcel = GetObject(, "Excel.Application")

WasExcelRunningBeforeThisExecution = (Err.Number = 0) ' if err.number = 0, true else false
Debug.Print Err.Number
Debug.Print Err.Description


Err.Clear

End Function


'====================================
Public Sub SaveExcelSpreadsheet()

On Error GoTo SaveExcelSpreadsheet_Err

   
Const cstrPath As String = "c:\EagleEhsVisits.xls"
   
Kill cstrPath

Set objExcelActiveWkb = objExcel.ActiveWorkbook

objExcelActiveWkb.SaveAs Filename:=cstrPath, FileFormat:=xlNormal
objExcelActiveWkb.Save

objExcel.DisplayAlerts = False
objExcelActiveWkb.Close savechanges:=False
objExcel.DisplayAlerts = True

Set objExcelActiveWS = Nothing
Set objExcelActiveWkb = Nothing

SaveExcelSpreadsheet_Exit:
     Exit Sub
   
SaveExcelSpreadsheet_Err:
     Select Case Err.Number
       
         Case 53     ' kill didn't find the file - ignore error
            'MsgBox Err.Number & "  " & Err.Description
            Resume Next
           
         Case Else
            MsgBox "Error # " & Err.Number & ": " & Err.Description
            Resume SaveExcelSpreadsheet_Exit
           
       End Select


End Sub

'==================================
Public Sub CloseExcel(blnHowToCloseExcel As Boolean)

On Error GoTo CloseExcel_Err


' objExcelActiveWkb.Close savechanges:=False
 
objExcel.DisplayAlerts = False
If Not blnExcelAlreadyRunning Then
    objExcel.Quit
End If
objExcel.DisplayAlerts = True

CloseExcel_Exit:
'    Set objExcelActiveWkb = Nothing
    Set objExcel = Nothing

   
    Exit Sub
   
CloseExcel_Err:
    MsgBox "Error # " & Err.Number & ": " & Err.Description
    Resume CloseExcel_Exit
 
   
End Sub





'====
Public Sub ImportTextToExcel2()

 objExcel.Workbooks.OpenText _
       Filename:="C:\EHSPMMt.TXT", Origin:=xlWindows, StartRow _
        :=1, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 2), Array(36, 2), Array _
        (45, 2), Array(52, 1), Array(60, 2), Array(86, 2), Array(121, 2), Array(146, 2), Array(150, _
        2), Array(152, 2), Array(161, 2), Array(163, 2), Array(174, 2), Array(186, 2), Array(197, 2 _
        ), Array(207, 2), Array(208, 2), Array(209, 2), Array(210, 2), Array(212, 2), Array(214, 2) _
        , Array(221, 2), Array(222, 2), Array(230, 2), Array(240, 2), Array(247, 2), Array(248, 2), _
        Array(250, 2), Array(261, 2), Array(270, 2), Array(280, 2), Array(290, 2), Array(297, 2), _
        Array(298, 2), Array(300, 2), Array(310, 2), Array(320, 2), Array(328, 2), Array(329, 2), _
        Array(330, 2), Array(334, 2), Array(340, 2), Array(341, 2), Array(410, 2), Array(480, 2), _
        Array(481, 2), Array(499, 2), Array(519, 2), Array(520, 2), Array(521, 2), Array(522, 2), _
        Array(530, 2))
       
       
' Exit Sub

Set objExcelActiveWS = objExcel.ActiveSheet

With objExcelActiveWS

           With .Rows("1:1")
              .Select
              .EntireRow.Insert
           End With

              .Cells(1, 1).Value = "header"
              .Cells(1, 2).Value = "filler1"
              .Cells(1, 3).Value = "patientNumber"
              .Cells(1, 4).Value = "filler2"
              .Cells(1, 5).Value = "PatientName"
              .Cells(1, 6).Value = "PatientStreet"
              .Cells(1, 7).Value = "PatientCity"
              .Cells(1, 8).Value = "PatientCounty"
              .Cells(1, 9).Value = "PatientState"
              .Cells(1, 10).Value = "PatientZip"
              .Cells(1, 11).Value = "PatienCountry"
              .Cells(1, 12).Value = "filler3"
              .Cells(1, 13).Value = "PatientPhone"
              .Cells(1, 14).Value = "PatientSSn"
              .Cells(1, 15).Value = "PatientDOB"
              .Cells(1, 16).Value = "G1"
              .Cells(1, 17).Value = "M1"
              .Cells(1, 18).Value = "filler4"
              .Cells(1, 19).Value = "R1"
              .Cells(1, 20).Value = "Rel"
              .Cells(1, 21).Value = "Chart#"
              .Cells(1, 22).Value = "E1"
              .Cells(1, 23).Value = "Medicare#"
              .Cells(1, 24).Value = "Medicaid#"
              .Cells(1, 25).Value = "filler5"
              .Cells(1, 26).Value = "E2"
              .Cells(1, 27).Value = "filler6"
              .Cells(1, 28).Value = "filler7"
              .Cells(1, 29).Value = "filler8"
              .Cells(1, 30).Value = "filler9"
              .Cells(1, 31).Value = "filler10"
              .Cells(1, 32).Value = "filler11"
              .Cells(1, 33).Value = "T1"
              .Cells(1, 34).Value = "filler12"
              .Cells(1, 35).Value = "filler13"
              .Cells(1, 36).Value = "filler14"
              .Cells(1, 37).Value = "filler15"
              .Cells(1, 38).Value = "I1"
              .Cells(1, 39).Value = "filler16"
              .Cells(1, 40).Value = "filler17"
              .Cells(1, 41).Value = "filler18"
              .Cells(1, 42).Value = "U1"
              .Cells(1, 43).Value = "filler19"
              .Cells(1, 44).Value = "filler20"
              .Cells(1, 45).Value = "U2"
              .Cells(1, 46).Value = "filler21"
              .Cells(1, 47).Value = "E3"
              .Cells(1, 48).Value = "I2"
              .Cells(1, 49).Value = "R2"
              .Cells(1, 50).Value = "A2"
              .Cells(1, 51).Value = "UDATE"


 
     With .Cells
          .Select
          .EntireColumn.AutoFit
     End With

   
 
 
End With
 
 
End Sub


Public Sub ImportSpreadsheetToAccess()


Dim strExcelFile As String
Dim strTableName As String

Dim strSql       As String

strExcelFile = "c:\EagleEhsVisits.xls"
strTableName = "T_EagleEhsVisits2"
       
strSql = "DELETE FROM " & strTableName
CurrentDb.Execute (strSql)

DoCmd.TransferSpreadsheet _
      TransferType:=acImport, _
      SpreadsheetType:=8, _
      TableName:=strTableName, _
      Filename:=strExcelFile, _
      HasFieldNames:=True

End Sub








0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now