How do I Break the 65000 Excel import barrier?

[talboyd01]

All,

At work, we run audits in Access on Class V switch records that are sent to us in .txt files and daily we encounter files that contain between 300,000 and 1 million records. Since the telephone numbers are stored as Numbers and not Text some TNs don't have all their zeros. For example, TN 555-123-0123 is stored as 555-123-123.

To get around this, we've been pulling the numbers into Excel, customizing the cells to handle all the zeros and exporting back to Access. Needless to say, with a million numbers Excel can't handle it. At 65000 records, it stops.

Is there a macro or script I can write for Excel that will import and at 65000 roll over to the next tab? I'm used to working in Access, but haven't found a way to add the zeros without going to Excel and back to Access. Anyone have any ideas for expediting this process?

Thanks in advance,

Talmadge Boyd

 

[euskadi]

Talboyd01,

I don't think you can accomplish this in Excel...

But, Access should be flexible enough to do what you're looking for without hitting Excel. Rather than import the text as a number into Access, I recommend you import it as text.

If you can't do that, you should still be able to write a function to restore the missing zeros to your phone numbers. Since NPA's and NXX's never start with a zero, this should do it:

(assuming your phone #s are numbers without dashes)

Function FixNumber(mynum)
    mynum = Left(mynum, 6) & Left("0000", 10 - Len(mynum)) & Right(mynum, Len(mynum) - 6)
    FixNumber = mynum
End Function

 

[talboyd01]

Thanks! I'll give this a shot. We usually to bring everything in as text if we can. Sometimes, it doesn't always work, but that's another issue.

Will this handle other instances like 0012 and 0001 as well?

Thanks again,

T

 

[Chance1234]

if you are mad enough to try the excel approach then your code would look somehting like this from Access

Sub Imverymad

Dim Mydb as database
Dim Myrec as recordset
Dim Wrkbk as workbook
Dim IntSht as integer
Dim RwCnt as integer
Set Wrkbk = createobject("excel.application&quot

Set Mydb = currentdb()
set Myrec = mydb.openrecordset("YourTable&quot

intSht = 1
rwcnt = 1

Do until Myrec.eof = true

if rwcnt = 65000 then
rwcnt = 1
intsht = intsht + 1
end if

with wrkbk.sheets(i)
.cells(rwcnt,1) = myrec![ID]
.Cells(rwcnt,2) = myrec![telephone Number]
end with

rwcnbt rwcnt +1

myrec.movenext
loop

 

[euskadi]

The function I included there will take any NPANXXXXXX number and look to see if it's 10 digits long. If the number is short, it will add the appropriate # of 0s 0XXX, 00XX, or 000X to correct the number.