Splitting memo field entry into rows

[danlin99]

Hi-

I'm trying to export a table from Access to SQL Server with a memo field
where some entries have more than 8000 characters. SQL does not have a memo
datatype but has varchar with a maximum of 8000 char. The memo field
contains comments for different dates with a hard return or <ctrl><enter> at
the end as shown in the example below. I would like to separate the memo
field into a new table with each RMA_Id, date and comment combo treated as a
new row. Can somebody help with VB code that will do this. TIA.

RMA_ID STATUS_COMMENT
123 1/1/06 - comment <ctrl><enter> 1/2/06 - comment
<ctrl><enter> 1/3/06 - comment
124 1/10/06 - comment <ctrl><enter> 1/15/06 - comment


New Table should lokk like this

RMA_ID STATUS_COMMENT
123 1/1/06 - comment
123 1/2/06 - comment
123 1/3/06 - comment
124 1/10/06 - comment
124 1/15/06 - comment

 

[Sergey Poberezovskiy]

You could use VBA.Strings.Split function to get separate lines of your text:

Dim i As Integer
Dim lines As Variant
....
lines = Split(Nz(YourMemoField, ""), vbCrLf)
For i = LBound(lines) To UBound(lines)
' insert into new table
Next
....

HTH

 

[John Spencer]

MS SQL Server has a field type TEXT which can store up to 2 gigs of data.
That said, I thing you are wise to split out the data into a new table as
you propose. I would also look at attempting to split out the date into a
separate column.

You could use the SPLIT function to break the field into its parts based on
Chr(13) & Chr(10) as the delimiter.
Once that is done you could then examine the individual array members to see
if the leading characters represented a valid date and put that into a date
field and the remaining portion into the comment field.

 

[danlin99]

You could use VBA.Strings.Split function to get separate lines of your text:

Dim i As Integer
Dim lines As Variant
...
lines = Split(Nz(YourMemoField, ""), vbCrLf)
For i = LBound(lines) To UBound(lines)
' insert into new table
Next
...

HTH

 

[danlin99]

I tried datatype text but the max length is 16. Thanks for your help.

 

[danlin99]

Thank you.

You could use VBA.Strings.Split function to get separate lines of your text:

Dim i As Integer
Dim lines As Variant
...
lines = Split(Nz(YourMemoField, ""), vbCrLf)
For i = LBound(lines) To UBound(lines)
' insert into new table
Next
...

HTH

 

[Sergey Poberezovskiy]

16 is not the field size - it's the pointer size - similar to that of Memo
field in Access. The actual field data is not stored at the same place as the
whole table is, and therefore the database needs a way to find that data. The
only comment I would add is that you have to deal with text (or image) type
fields in SQL Server in a slighlty different syntax that of other data types
- but they can be extremely useful when storing large chunks of data...