Import data into seperate columns

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.


"naulerich" wrote in message
...
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" wrote:

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?

"naulerich" wrote in message
...
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" wrote:

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?

Emailed you the response from the BA.

"Doug Kanter" wrote:

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.


"naulerich" wrote in message
...
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" wrote:

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?

"naulerich" wrote in message
...
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" wrote:

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?

On Mon, 3 Apr 2006 08:50:01 -0700, naulerich
wrote:

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, altF11 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