Import data into seperate columns

[David Biddulph]

Unless I misread the original post, he said there were commas WITHIN the
text that he wanted to keep intact - not just commas as delimiters. If
this is the case, Excel will split fields based not only on the commas
which exist as delimiters, but also when it sees those within the text.

No. That's what the text qualifier sorts out for you. Try it.

 

[David Biddulph]

Now, that's interesting. The test strings I used were numerical, and
looked like the line below:
"1234,5678,9" Excel broke that into three columns. I wonder if it behaves
differently when the strings in question are numerical.

Interesting, as you say. I found that it kept the 1234,5678,9 together if
the preceding double quote mark is immediately after the comma, but not if
there is a space between.

The first line here keeps the numeric string together, but the second line
splits it:
"123","test, text","this is a test","abc","1234,5678,9"
"123","test, text","this is a test","abc", "1234,5678,9"

 

[Dave Peterson]

If I included the space character (as well as the comma), then both lines

"123","test, text","this is a test","abc","1234,5678,9"
"123","test, text","this is a test","abc", "1234,5678,9"

were imported the same--the last field was 1234,5678,9 (all one cell).

 

[Doug Kanter]

No. That's what the text qualifier sorts out for you. Try it.

In theory, yes, but the OP e-mailed me some of her original text (with
commas, not tildes), and doing what you suggest, the import does NOT work
correctly. Excel sees the commas WITHIN the text strings as delimiters, same
as the ones BETWEEN the text strings. Here's the text she sent:

"5493", "BASE-(Claims) Auto Format Update I", "Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats we
must prep for migrating sites to the latest 4E format. This requirement is
in sync with the EDI requirement with the same name. This is a multi-step
project.", "Today most of our sites are utilizing the WEBMD_NSF Format. To
ensure that all sites are prepared for MSP, electronic secondary, and using
the latest claims submission formats we must prep for migrating sites to the
latest 4E format. This requirement is in sync with the EDI requirement with
the same name. This is a multi-step project.", "<<internal source group>>",
"Have to Have", "", ""

"5501", "BASE_Patient/Account Alert in Check-in", "Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them to
miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.", "Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.", "<<internal source group>>",
"Nice to Have", "", ""

"3943", "BASE-Account Select Window Default Method", "Intergy today allows
users to designate their user preference when selecting patients. Users
would like to have this same feature when selecting accounts. By providing
the user to designate their "default" account select method in user
preference, it will allow them to save key strokes in the account select
windows.", "Provide the same method in Patient Select Window -- ability for
the user to default their search.
In the User Preference for Patient Select we called the Preference --
"Select Patient Sort/Filter Default". Need to do the same for Account
Select.", "<<internal source group>>", "Nice to Have", "", ""

 

[Doug Kanter]

Nichole, using the sample text you e-mailed me, I got the same results you
did. This leads to another question - same one I asked yesterday:

Do you have any control over the program which PRODUCES the text file? What
is that program?

 

[Dave Peterson]

I think that the OP will have to clean up the data.

There shouldn't be spaces between fields and any field that contains double
quotes will have to have those double quotes doubled up.

In that last line,
their "default" account
would have to become
their ""default"" account

I don't see a way around it except to clean up that data.

 

[David Biddulph]

In theory, yes, but the OP e-mailed me some of her original text (with
commas, not tildes), and doing what you suggest, the import does NOT work
correctly. Excel sees the commas WITHIN the text strings as delimiters,
same as the ones BETWEEN the text strings. Here's the text she sent:

"5493", "BASE-(Claims) Auto Format Update I", "Today most of our sites are
utilizing the WEBMD_NSF Format. To ensure that all sites are prepared for
MSP, electronic secondary, and using the latest claims submission formats
we must prep for migrating sites to the latest 4E format. This requirement
is in sync with the EDI requirement with the same name. This is a
multi-step project.", "Today most of our sites are utilizing the WEBMD_NSF
Format. To ensure that all sites are prepared for MSP, electronic
secondary, and using the latest claims submission formats we must prep for
migrating sites to the latest 4E format. This requirement is in sync with
the EDI requirement with the same name. This is a multi-step project.",
"<<internal source group>>", "Have to Have", "", ""

"5501", "BASE_Patient/Account Alert in Check-in", "Patient Alerts do not
currently work in Patient Check-in. So unless the front desk brings up the
detail on the patient, alerts are not presented to the user causing them
to miss critical items at the first contact with the patient.

By presenting this alert when the patient is chosen on the check in list,
the user will make or take the proper action necessary based on the alert
message.", "Need to activate Patient and Account Alert during patient
check-in.
Not currently working in Patient Check-in.", "<<internal source group>>",
"Nice to Have", "", ""

"3943", "BASE-Account Select Window Default Method", "Intergy today allows
users to designate their user preference when selecting patients. Users
would like to have this same feature when selecting accounts. By providing
the user to designate their "default" account select method in user
preference, it will allow them to save key strokes in the account select
windows.", "Provide the same method in Patient Select Window -- ability
for the user to default their search.
In the User Preference for Patient Select we called the Preference --
"Select Patient Sort/Filter Default". Need to do the same for Account
Select.", "<<internal source group>>", "Nice to Have", "", ""

If you change each occurrence of , " [comma, space, double quote] to ,"
[comma, double quote], then it will allow the import wizard to pick up the
double quote text qualifier art the start of each field, and hence it won't
split the field at a comma within the double quotes.

The only remaining problem is the additional double quotes around "default"
in the final data set. As Dave Peterson has said, in that case you'll need
to double up the double quotes. It then works OK.
--

 

[Guest]

The text is coming out of a Borland database (Caliber), it will export to
Word no problem however it is requested the output be in .xls format which is
not supported through the tools we have purchased through Borland. I am not
sure what control the BA has, other than setting delimiters when
exporting...What can I ask that the BA do to get a clean export?

 

[Doug Kanter]

Most database products allow the user to design outbound reports in any way
that's necessary. Although programmers are as likely to do stupid things as
the rest of the population, I'd be very surprised if Borland built that
product without the same report design features as their older products
(Paradox, Delphi).

This link takes you to the Borland support forums - I'd ask there about
whether you can design an export report that's more manageable than the file
you're struggling with now:
http://support.borland.com/category.jspa?categoryID=3

This might take the discussion into another direction, but what's the final
purpose of bringing all that text into Excel?

 

[Guest]

I know that the capability to export to Excel is there however, I have been
told that is outside of the reporting functionality we purchased. Bottom
line, we are trying to find a work around, currently the data is being
maniputlated close to 20 hrs per output (not sure what takes so long...) to
get into the pretty excel colums. I believe that the final reason for the
output in excel is for R&D tracking of projects/deliverables. My comapny
LOVES excel, spreadsheet heaven here...

Can't very well ask Borland what to do...

 

[Doug Kanter]

Is there a name for the reporting capability that you DO have? A product
description? I know some ancient Borland product veterans who might have
some tricks up their sleeves, but I'd need to tell them what you've got.

 

[Guest]

Emailed you the response from the BA.

Is there a name for the reporting capability that you DO have? A product
description? I know some ancient Borland product veterans who might have
some tricks up their sleeves, but I'd need to tell them what you've got.

 

[Ron Rosenfeld]

I have a .txt file that looks like:

"123","test text","this is a test","abc"

I want my output in excel to be the following:

A1=123
B1=test text
C1=this is a test
D1=abc

Potential problem is that there are commas through out the "test text" and
"this is a test" columns therefore text to columns wont work. However what I
need to show in each separate column is enclosed in quotes.

Any help would be appreciated!

Having read a bunch of this thread, it seems to me that you may be able to
parse out your data using Regular Expressions in VBA.

To enter the code, <alt><F11> opens the VBA Editor. Ensure your project is
highlighted in the project explorer window, then Insert Module and paste the
code below into the window that opens. In the VB Editor, you must also select
Tools/References and select Microsoft VBScript Regular Expressions 5.5 from the
list.

To use this, with your data in A1, enter the following formula into B1 and
copy/drag across as far as required.

=remid($A1,"""[^""]+""",COLUMNS($B:B))

The Regular Expression portion: """[^""]+""" says generate a match that
begins with a double quote; is followed by any number of characters that do not
include a double quote; and terminated by a double quote.

The Columns function is merely a method of generating an increasing number as
you copy/drag the formula across several columns; and it will cause the
expression to return the 1st, 2nd, etc instance of the matched pattern.

The resultant string will have double quotes around it. If that is not
satisfactory, you can embed the above in a SUBSTITUTE function to get rid of
them.

=SUBSTITUTE(remid($A1,"""[^""]+""",COLUMNS($B:B)),"""","")

============================================
Option Explicit
Function REMid(str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True) _
As Variant 'Variant as value may be string or array

Dim objRegExp As RegExp
Dim objMatch As Match
Dim colMatches As MatchCollection

Dim i As Long 'counter
Dim t() As String 'container for array results

' Create a regular expression object.
Set objRegExp = New RegExp

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Test whether the String can be compared.
If (objRegExp.Test(str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim t(1 To UBound(Index))
For i = 1 To UBound(Index)
t(i) = colMatches(Index(i) - 1)
Next i
REMid = t()
Else
REMid = CStr(colMatches(Index - 1))
If IsEmpty(REMid) Then REMid = ""
End If
On Error GoTo 0 'reset error handler
Else
REMid = ""
End If
End Function
=============================

Let me know if this works for you.



--ron