Community
Showing results for 
Search instead for 
Do you mean 
Reply

*.csv import fails with COMPANYNAME field

New Member
Posts: 9
Country: United States

*.csv import fails with COMPANYNAME field

[ Edited ]

Hello

 

I tried to import a CSV file that looks like this:

 

"Company1","012 345 67 89","987 654 32 10",,"Street1",,"Place1","PLZ1","Country1"

"Company2","012 345 67 89","987 654 32 10",,"Street2",,"Place2","PLZ2","Country2"
"Company3","012 345 67 89","987 654 32 10",,"Street3",,"Place3","PLZ3","Country3"

 

So I use the import wizard in order to get the data into ACT. Everything works as expected, I map the columns to the ACT database fields. My *.map (xml) File looks as follows:

 

<?xml version="1.0"?>
<FieldMap>
  <FieldRecordMap DataSetName="Arbitrary" FirstRecordIsHeader="true">
    <RecordMap Name="Arbitrary">
      <FieldMetaData Name="Column01" Display="Column01" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column02" Display="Column02" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column03" Display="Column03" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column04" Display="Column04" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column05" Display="Column05" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column06" Display="Column06" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column07" Display="Column07" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column08" Display="Column08" ... IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Column09" Display="Column09" ... IsReadonly="False" LUID="-1" />
    </RecordMap>
  </FieldRecordMap>
  <FieldRecordMap DataSetName="Company" FirstRecordIsHeader="true">
    <RecordMap Name="Company" />
  </FieldRecordMap>
  <AssociationMap>
    <Association>
      <Field Name="Column01" />
      <Field Name="CompanyContactNames" />
    </Association>
    <Association>
      <Field Name="Column02" />
      <Field Name="BUSINESS_PHONE" />
    </Association>
    <Association>
      <Field Name="Column03" />
      <Field Name="FAX_PHONE" />
    </Association>
    <Association>
      <Field Name="Column04" />
      <Field Name="WEBADDRESS" />
    </Association>
    <Association>
      <Field Name="Column05" />
      <Field Name="BUSINESS_LINE1" />
    </Association>
    <Association>
      <Field Name="Column06" />
      <Field Name="BUSINESS_LINE2" />
    </Association>
    <Association>
      <Field Name="Column07" />
      <Field Name="BUSINESS_CITY" />
    </Association>
    <Association>
      <Field Name="Column08" />
      <Field Name="BUSINESS_POSTALCODE" />
    </Association>
    <Association>
      <Field Name="Column09" />
      <Field Name="BUSINESS_COUNTRYNAME" />
    </Association>
  </AssociationMap>
</FieldMap>

 

 

Fine, but after the import was finished there were no company names instead there was something like: Importiertenfirma 3ad42keo-l9eh-78dw-26dw-hdz37skf51by (keep in mind, my ACT installation is German, so in English it would be something similar to importedcompany [hash])

 

Since the ACT database is locked, I tried to find all Fields and their corresponding column name on the net - luck there. Then I noticed that the Company field has another column name than my ACT installation wanted to use. I changed it to COMPANYNAME, but still there are those "hashes" instead of names. When I map the first column I have no "Company" (Firma) field, I tried all fields that were related to company but that didn't help it either. I'm just able to map the first column to those 3 fields that are related to "Company:" (Firma: ) CompanyContactNames, DynamicMembership or DESCRIPTION.

As far as I see all the other fields have been inserted correctly it's just the company (or Firma in German) that doesn't want to work. Could that be some kind of bug? (German localization) or what am I doing wrong? My SQL Server is English, since this was necessary to install ACT.

 

Thanks in advance

Rico

 

Edit: I talked to a support technician from Sage USA, and he told me, that this is a known issue I should install ACT 10.0.3. So I updated all my ACT installations, but still no "Company" field! Any other suggestions?

Message Edited by r.kerster on 09-19-2008 10:18 AM

My ACT Version: 10.0.2 (German)
My ACT License: Premium EX
My SQL Server: MS SQL 2005 Developer Edition SP3 (English)
My Operating System: Windows 2003 Server SP2

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: *.csv import fails with COMPANYNAME field

You could try the ACTDIAG fields report to see if the German field names are different as per this ACT! Knowledge Base article - http://tinyurl.com/4k6th5
New Member
Posts: 9
Country: United States

Re: *.csv import fails with COMPANYNAME field

Thank you Mike

This is what actdiag gave me:

 

CUSTOMER Database Fields Report from ACTDiag
Sep 22 2008  8:03AM
Datenbankproduktversion: 9.00.3042.00
Name des Datenbankservers: SERVER\ACT7
Database Collation: Latin1_General_CI_AS
Schemaversion: 10.03.0182.0000
Schemadatum:  2008-09-19 16:56:01.983
Ablageort der Datenbank: C:\ACT\CUSTOMER.ADF
 


------------------------------
ENTITY RELATIONSHIP DETAIL:
------------------------------

 

-----------------------------------------------------------------------------------------------------------------------------
       Entity Name: COMPANY                                                
    Entity Display: Firma                                                  
        Table Name: TBL_COMPANY                                            
     Table Display: Firma                                                  
Association Column: COMPANYID                                               

 

So I'd use "COMPANY", because there's no field with the name COMPANYFIELD. I changed my map xml file and tried again - didn't work. The weird thing is, I never see "company" in the dropdown list:

 

 Importassistant

 

But no "Company" field I could select. Those 3 just got something to do with the Company but none of them refers to the Company field in Act.

 

Just to show you what comes out afterwards:

ACT with incorrect import 

 

I'm quite clueless, does anyone have an idea?

 

thanks in advance

Rico


My ACT Version: 10.0.2 (German)
My ACT License: Premium EX
My SQL Server: MS SQL 2005 Developer Edition SP3 (English)
My Operating System: Windows 2003 Server SP2

Moderator
Posts: 4,395
Country: USA

Re: *.csv import fails with COMPANYNAME field

You'll need to edit the CSV so that it will auto map to the company field.  So you'll need to add a header row (if there isn't already) and for whatever column is company name, put in Firma

New Member
Posts: 9
Country: United States

Re: *.csv import fails with COMPANYNAME field

[ Edited ]

Okay this is my *.cvs file exactly as I try to import it (I use just one company because I got tired of deleting around 1000 companies after the import has failed so many times)

 

"Firma","Telefon","Fax","Website","Strasse 1","Strasse 2","Ort","PLZ","Land"
"Company AG","012 345 67 89","012 345 67 12",,"Irgendeinestr. 17",,"Kanton","1234","CH"

 

 I open up the importwizard point to my csv file, select import to company datarecords and the dialogue where I map the fields looks as follows:

 

 

 

Fine now I load my *.map File  which looks exactly like this here:

 

<?xml version="1.0"?>
<FieldMap>
  <FieldRecordMap DataSetName="Arbitrary" FirstRecordIsHeader="true">
    <RecordMap Name="Arbitrary">
      <FieldMetaData Name="Firma" Display="Firma" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Telefon" Display="Telefon" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Fax" Display="Fax" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Website" Display="Website" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Strasse 1" Display="Strasse 1" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Strasse 2" Display="Strasse 2" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Ort" Display="Ort" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="PLZ" Display="PLZ" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
      <FieldMetaData Name="Land" Display="Land" FieldType="STRING" IsVisible="True" IsReadonly="False" LUID="-1" />
    </RecordMap>
  </FieldRecordMap>
  <FieldRecordMap DataSetName="Company" FirstRecordIsHeader="true">
    <RecordMap Name="Company" />
  </FieldRecordMap>
  <AssociationMap>
    <Association>
      <Field Name="Firma" />
      <Field Name="COMPANY" />
    </Association>
    <Association>
      <Field Name="Fax" />
      <Field Name="FAX_PHONE" />
    </Association>
    <Association>
      <Field Name="Land" />
      <Field Name="BUSINESS_COUNTRYNAME" />
    </Association>
    <Association>
      <Field Name="Ort" />
      <Field Name="BUSINESS_CITY" />
    </Association>
    <Association>
      <Field Name="PLZ" />
      <Field Name="BUSINESS_POSTALCODE" />
    </Association>
    <Association>
      <Field Name="Strasse 1" />
      <Field Name="BUSINESS_LINE1" />
    </Association>
    <Association>
      <Field Name="Strasse 2" />
      <Field Name="BUSINESS_LINE2" />
    </Association>
    <Association>
      <Field Name="Telefon" />
      <Field Name="BUSINESS_PHONE" />
    </Association>
    <Association>
      <Field Name="Website" />
      <Field Name="WEBADDRESS" />
    </Association>
  </AssociationMap>
</FieldMap>

 

 

After loading the map File my dialogue looks like this here:

 

 

Very odd things in both screens: Firma has a quotation mark in front -> "Firma seems like some parsing gone wrong. Anything wrong with my File? There's just one quotation mark wrapped around every Text.

 

Second, why does it map Fax to Firma? that's something I really don't understand, the *.map file tells it to map Fax to FAX_PHONE and not COMPANY...import fails the same way.

 

I hope someone can shed some light on this as I'm getting more and more confused

 

Thanks in advance

Rico

Message Edited by r.kerster on 09-23-2008 02:03 AM

My ACT Version: 10.0.2 (German)
My ACT License: Premium EX
My SQL Server: MS SQL 2005 Developer Edition SP3 (English)
My Operating System: Windows 2003 Server SP2

Platinum Elite Contributor
Posts: 14,384
Country: Australia

Re: *.csv import fails with COMPANYNAME field


r.kerster wrote:

Thank you Mike

This is what actdiag gave me:

 

CUSTOMER Database Fields Report from ACTDiag
Sep 22 2008  8:03AM
Datenbankproduktversion: 9.00.3042.00
Name des Datenbankservers: SERVER\ACT7
Database Collation: Latin1_General_CI_AS
Schemaversion: 10.03.0182.0000
Schemadatum:  2008-09-19 16:56:01.983
Ablageort der Datenbank: C:\ACT\CUSTOMER.ADF
 



 

No, that's not what I meant... have a look further down for something like:

 

----------------------------------------------------------------------------------------------------
     ACT! Field: Company                                           ACT! Fieldtype: Character(128)
Physical Column: NAME                                           Physical Datatype: NVARCHAR(128)
  OLE/DB Column: COMPANY Company
          Alias: COMPANY
 Min Column Len: 32
      Collation: Latin1_General_CI_AS
  Created On/By: May 24 2006  6:39AM / ACT! System
         Custom: 0    Displayed: 1    Define Fields: 1    Ordinal: 2  
   Allow Blanks: 0      Primary: 1   Record History: 0
      Protected: 0   Can Resize: 1       Can Delete: 0
 Default Access: FULL ACCESS        Valid Access Levels: 4 (FA)
    Access List: All Users(2)
  Initial Value:
       Picklist:
    On Focus Trigger:
  Lost Focus Trigger:
   On Change Trigger:
----------------------------------------------------------------------------------------------------

Moderator
Posts: 4,395
Country: USA

Re: *.csv import fails with COMPANYNAME field

Remove the quotation mark from Firma in the CSV file.
New Member
Posts: 9
Country: United States

Re: *.csv import fails with COMPANYNAME field

[ Edited ]

Sorry was heavily busy these days. But I managed to solve this issue.

The Name of the field is not COMPANY nor COMPANYNAME it's just "NAME" (what a coincidence).. so I load my (manually edited) *.map file and the field "Firma" magically appears and ACT imports the company data into the right field!

 

Problem solved!

 

Thank you Mike and dlunceford for your support and patience. I appreciate your effort.

Regards

Rico

 

Message Edited by r.kerster on 10-07-2008 02:25 AM

My ACT Version: 10.0.2 (German)
My ACT License: Premium EX
My SQL Server: MS SQL 2005 Developer Edition SP3 (English)
My Operating System: Windows 2003 Server SP2