Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2008
    Location
    Burlington, MA
    Posts
    2

    Unanswered: Help importing data archives

    I received 2 files on 2 CD-ROMs. They were from a 4mm tape and put onto CD-ROM for space reasons. Each has a title and .001 or .002 at the end. I used a program called HJ Split to combine these 2 files. The combined file is about 800MB in size. When combined it has no file extension at the end.

    I am trying to read it into Access. I have a file structure document that tells me how many characters are in each variable. Whenever I try to open in Access, before I can specify the file structure, I get read error messages.

    I tried to add a .txt extension to trick Access into importing, but no luck.

    Any ideas on ways around or other programs that would be better suited to import into?

    Thanks

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Difficult to say. A .001 and .002 extension usually stems from some type of backup program where it split the file into 2 parts to fit onto the destination drive (for example, winzip will split a big file into 2 or more parts if it can't fit the entire file onto the destination drive.) If you've successfully put the 2 files together into 1 file, the question is was this done correctly and if so, does another program need to be run to convert or extract the combined file into another data file? I'm guessing another program would need to be run to extract the data out of the combined file but it's difficult to say. Adding a .txt extension to the 1 file is a good try but it sounds like it wasn't a simple txt file and you'll most likely need the program that was used to create this file (probably some type of backup program since it was put onto a tape originally). If you knew what program was used to create this file, it would be a simple matter of extracting this file back into the data file before it was created but I'm guessing you need to know what program was used to create this file. 800 mb is an extremely large file (with a LOT of data if it's just 1 data file) but again, I'm guessing it's some type of backup file which is comprised of several other files, the data file being just one of them among many within that bunch of files.

    Others might have some utilities to suggest you could use on this file. See if you can find out what kind of backup programs are used at that company and perhaps try restoring the file from the backup program used. Without an extension (or .001 and .002), it just makes it that much more difficult to determine the program used.
    Last edited by pkstormy; 01-19-08 at 04:07.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Id side with Paul, ideally you need to get the data reprocessed back through the same mechanism as created it in the first place.

    the main issue is going to be how data has been stored, for example what storage is being used (ASCII text or binary), what compression is used, what encyrption (if any).

    If you cannot find the original program, then I'd be tempted to see if I can see/read the file. 800mb is way to big for any editor I'm aware of, so you may need to knock up a quick and dirty binary / ascii file viewer (with a layout similar to hex editors. Heck you may be able to tell if the file is legible by issuing type <filename> in a file console window

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    WinRAR splits files as you say, in .001 and .002 file types also. You can try that too. I know when I've used HJSplit, it'll take the extensions off of some files and then you run into the same problem. However, the files that I have had this happen to are usually just like regular TXT or NFO / SFV files.

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    in any .MDB (an empty one for example)
    Database Window >>> Objects >>> Modules, New

    paste this:

    public sub SnipFile()
    const fileSource as string = "c:\here\there\myfile.txt" 'adjust to suit your 800MB monster
    const fileSink as string = "c:\temp\snippet.txt 'adjust to suit where you want a small file saved
    const lngSink as long = 10000 'adjust to suit how big (Bytes) you want the small file to be
    const fsoRead as integer = 1
    const fsoWrite as integer = 2
    dim fso as object
    dim lFile as object
    dim strBuf as string
    set fso = createobject("scripting.filesystemobject")
    set lFile = fso.opentextfile(fileSource, fsoRead, false, 0)
    strBuf = lFile.read(lngSink)
    lFile.close
    set lFile = fso.opentextfile(fileSink, fsoWrite, true, 0)
    lFile.write strBuf
    lFile.close
    set lFile = nothing
    set fso = nothing
    end sub

    then Ctrl-G and in the resulting immedate window type
    snipfile
    and hit return.

    did it work?
    can you open fileSink in Notepad?
    does it conform with the description document?
    if OK, then still in notepad, trim off the fat from the end of the snippet so you end on the end of a record.
    save from Notepad as c:\temp\trimSnippet.txt and close

    back in Access, see if you can getexternaldata/import trimsnippet.txt
    ...if that seems ok, save the import specification.
    then try importing the 800MB monster using the import specification you just saved.

    izy
    currently using SS 2008R2

  6. #6
    Join Date
    Jan 2008
    Location
    Burlington, MA
    Posts
    2

    Success.... so far

    Izy,

    Thanks. That seems to have worked. From that snippet, I can see the file structure is as expected and I can create the import specs to give to Access to run it in. Now I just have to upgrade from a 50K snippet to a 800MB monster, but I'll let you know.

    Many thanks
    JSid

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    so far so good.
    BUT
    Access is using unicode: text fields are doubled in size.
    Access dies at 2GB
    ...so you are at the limit!

    good luck with it.

    izy

    LATER:
    try first to import the whole lot as text (1.6GB), some of the other datatypes take significantly more than the two-byte/char of unicode
    Last edited by izyrider; 01-21-08 at 12:27.
    currently using SS 2008R2

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Nice code izy. I'll give it a try.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •