Comma separated

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mac_see - 09 Feb 2005 01:57 GMT

Hi!

In my table, I have only 1 field (Field1 - data type "Text" - size 42) with
thousands of records.

Every record is a combination of 10 numbers separated by comma and then a
space.

I want to find the sum of all 10 numbers in a record.

AND if the above is not possible,

I want to segregate all numbers and put them in 10 different fields of data
type "Byte"

Here is a sample data:

1, 3, 20, 22, 27, 30, 50, 53, 67
1, 4, 6, 10, 20, 23, 31, 48, 59
1, 5, 14, 27, 42, 44, 54, 64, 65
10, 11, 18, 26, 36, 37, 42, 46, 61

Can this be done using a query or VBA?

Maxi

Reply to this Message

Dave Patrick - 09 Feb 2005 02:19 GMT

Probably easiest to export the table as fixed width, text file. Then import
as *.csv

Signature

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

| Hi!
|
[quoted text clipped - 21 lines]
|
| Maxi

Reply to this Message

mac_see - 09 Feb 2005 03:09 GMT

Can you elaborate more on this please?

Are you referring to Excel? If yes, My records are more than 65536.

Maxi

> Probably easiest to export the table as fixed width, text file. Then import
> as *.csv
[quoted text clipped - 26 lines]
> |
> | Maxi

Reply to this Message

Dave Patrick - 09 Feb 2005 04:04 GMT

No to Excel. You can right-click your table, choose Export choosing type
(*.txt), then radio button for 'Fixed Width then Finish

Now File|Get External Data|Import then navigate to the file location, choose
type (*.txt) but this time choose Comma Delimited and into a new table.

Then create a query based on the new table2 something like;

SELECT
[Table2]![Field1]+[Table2]![Field2]+[Table2]![Field3]+[Table2]![Field4]+[Table2]![Field5]+[Table2]![Field6]+[Table2]![Field7]+[Table2]![Field8]+[Table2]![Field9]
AS Expr1
FROM Table2;

Signature

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

Reply to this Message

ed@breadwinner.com.au - 09 Feb 2005 06:53 GMT

Hello mac_see,
Try the code below. If you don't need to create the target table just
comment the first "db.Execute" line.
I must credit Ken Getz with the original code for the GetString
function. I had to modify his GetPart function to suit my needs.

Sub ParseMyNumbers()
On Error GoTo ErrorHandler
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim sTableName As String
Dim sSQL As String
Set db = CurrentDb
sTableName = "Table2"
sSQL = "CREATE TABLE " & sTableName & " (Field1 BYTE, Field2 BYTE, " _
& "Field3 BYTE, Field4 BYTE, Field5 BYTE, Field6 BYTE,
Field7 " _
& "BYTE, Field8 BYTE, Field9 BYTE, Field10 BYTE );"
'Debug.Print sSQL
db.Execute sSQL
'GoTo ThatsIt
sSQL = "SELECT CSNumbers FROM Table1;"
'Debug.Print sSQL
Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot) 'dbOpenDynaset
dbOpenForwardOnly
With rs
.MoveLast 'force error 3021 if no records
.MoveFirst
Do Until .EOF
sSQL = "INSERT INTO " & sTableName & " (Field1, Field2, Field3,
" _
& "Field4, Field5, Field6, Field7, Field8, Field9, Field10
) " _
& "VALUES (" _
& Trim(GetNumber(0, .Fields(0), ",")) & ", " _
& Trim(GetNumber(1, .Fields(0), ",")) & ", " _
& Trim(GetNumber(2, .Fields(0), ",")) & ", " _
& Trim(GetNumber(3, .Fields(0), ",")) & ", " _
& Trim(GetNumber(4, .Fields(0), ",")) & ", " _
& Trim(GetNumber(5, .Fields(0), ",")) & ", " _
& Trim(GetNumber(6, .Fields(0), ",")) & ", " _
& Trim(GetNumber(7, .Fields(0), ",")) & ", " _
& Trim(GetNumber(8, .Fields(0), ",")) & ", " _
& Trim(GetNumber(9, .Fields(0), ",")) _
& ");"
db.Execute sSQL
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
GoTo ThatsIt
ErrorHandler:
Select Case Err.Number
Case 3021 'No Records
Case Else
MsgBox "Problem with ParseMyNumbers()" & vbCrLf _
& "Error: & " & Err.Number & ": " & Err.Description
End Select
ThatsIt:
Set db = Nothing
End Sub

Function GetString(pPart As Integer, pValue As Variant, _
Optional sDivider As String) As String
'pPart is the section of pValue to be extracted
'pValue is the field or expression to be parsed
On Error GoTo ErrorHandler
Dim I As Integer
Dim sPart As String
Dim iTildeAt As Integer
Dim sNextChar As Variant

If Nz(sDivider, "") = "" Then
sDivider = "~"
Else
sDivider = sDivider
End If

If IsNull(pValue) Then
GetString = ""
Exit Function
End If

iTildeAt = 0
For I = 1 To pPart
iTildeAt = InStr(iTildeAt + 1, pValue, sDivider)
Next

'Extract the value
For I = iTildeAt + 1 To Len(pValue)
sNextChar = Mid$(pValue, I, 1)
If sNextChar = sDivider Then
Exit For
End If
sPart = sPart & sNextChar
Next
' MsgBox Val(sPart)
GetString = CStr(sPart)
Exit Function
ErrorHandler:
GetString = ""
MsgBox "Error " & Err.Number & ": " & Err.Description &
vbCrLf _
& "in GetString()"
Exit Function
End Function

I hope this helps,
Ed.

Reply to this Message

mac_see - 09 Feb 2005 18:01 GMT

Hi! Dave/Ed,

Dave, Your method works fine and it suits my needs. Just one question.

Will a .txt file handle 1 million+ records? and what is the limit for this?

Ed, Your code gives me compile error: "Invalid use of property" in the line
"dbOpenForwardOnly" Do you want me to make changes to this code before
running it?

PS: My table name is Table1

Maxi

> Hello mac_see,
> Try the code below. If you don't need to create the target table just
[quoted text clipped - 106 lines]
> I hope this helps,
> Ed.

Reply to this Message

Dave Patrick - 09 Feb 2005 18:31 GMT

I've only tested to a half million records or so. I think the limitation is
the lesser of the file system limits or Access. Access databases I believe
are limited to ~ 2 gB in size.

FYI;

From the resource kit;

Table 3.10 FAT16 Size Limits

Description Limit
Maximum file size 2^32 - 1 bytes
Maximum volume size 4 GB
Files per volume 2^16

Maximum Sizes on FAT32 Volumes
The FAT32 volume must have at least 65,527 clusters. The maximum number of
clusters on a FAT32 volume is 4,177,918. Windows 2000 creates volumes up to
32 GB, but you can use larger volumes created by other operating systems
such as Windows 98. Table 3.11 lists FAT32 size limits.

Table 3.11 FAT32 Size Limits

Description Limit
Maximum file size 2^32 - 1 bytes
Maximum volume size 32 GB (This is due to the Windows 2000 format utility.
The maximum volume size that Windows 98 can create is 127.53 GB). Files per
volume Approximately 4 million

Important

Windows 2000 can format new FAT32 volumes up to 32 GB in size but can mount
larger volumes (for example, up to 127.53 GB and 4,177,918 clusters from a
volume formatted with the limits of Windows 98). It is possible to mount
volumes that exceed these limits, but doing so has not been tested and is
not recommended.

Maximum Sizes on NTFS Volumes
In theory, the maximum NTFS volume size is 2^32 clusters. However, even if
there were hardware available to supply a logical volume of that capacity,
there are other limitations to the maximum size of a volume.

One of these limitations is partition tables. By industry standards,
partition tables are limited to 2^32 sectors. Sector size, another
limitation, is a function of hardware and industry standards, and is
typically 512 bytes. While sector sizes might increase in the future, the
current size puts a limit on a single volume of 2 terabytes (2^32 * 512
bytes, or 241 bytes).

For now, 2 terabytes should be considered the practical limit for both
physical and logical volumes using NTFS.

The maximum number of files on an NTFS volume is 2^32 - 1. Table 3.12 lists
NTFS size limits.

Table 3.12 NTFS Size Limits

Description Limit
Maximum file size 264 bytes - 1 KB (On disk format)
244 bytes - 64 KB (Implementation)

Maximum volume size 264 allocation units (On disk format)
2^32 allocation units (Implementation)

Files per volume 2^32 - 1

Signature

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

| Hi! Dave/Ed,
|
[quoted text clipped - 120 lines]
| > I hope this helps,
| > Ed.

Reply to this Message

Ed Adamthwaite - 09 Feb 2005 22:41 GMT

Hello Maxi,
The dbForwardOnly is part of the commented constants on the db.OpenRecordset
line. Your newsreader has added a line feed to wrap the text. In your reply
I see that this has happened elsewhere. Just check through the listing for
where this has happened and correct them.
Cheers,
Ed.

> Hi! Dave/Ed,
>
[quoted text clipped - 122 lines]
>> I hope this helps,
>> Ed.

Reply to this Message

mac_see - 10 Feb 2005 00:59 GMT

I tried looking for the rest of the line feeds but I am not getting it right.
Can you email me the code at mac_mahesh@hotmail.com?

Also do you want me to make changes to this code before
running it?

Maxi

> Hello Maxi,
> The dbForwardOnly is part of the commented constants on the db.OpenRecordset
[quoted text clipped - 130 lines]
> >> I hope this helps,
> >> Ed.

Reply to this Message